<?php

// +----------------------------------------------------------------------

// | Description: 客保部---员工评分和店面评分导出的页面逻辑

// +----------------------------------------------------------------------

// | Author: zl

// +----------------------------------------------------------------------



namespace app\crm\controller;



use app\admin\controller\ApiCommon;

use app\admin\model\ShopModel;
use app\crm\model\BalanceLog;

use app\crm\model\ReserveandLost;
use app\crm\model\Setting;

use app\crm\model\SettingCategory;

use app\crm\model\Service;

use app\crm\model\ReturnVist;

use app\crm\model\ReturnVistT;

use app\crm\model\Customer as CustomerModel;

use app\crm\model\TransferShop;

use app\crm\model\CustomerConfig as CustomerConfigModel;

use app\crm\model\CustomerGrade as CustomerGradeModel;

use think\response\Json;

use think\facade\Db;

use app\admin\model\Coupon as CouponModel;

use app\common\traits\ActionRecordTrait;

use app\crm\model\CustomerInfoAPI as CustomerInfoAPIModel;//引用EC接口文档


class StaffFenzhi extends ApiCommon

{
    protected $customerModel;

    protected $customerConfigModel;

    /**

     * 用于判断权限

     * @permission 无限制

     * @allow 登录用户可访问

     * @other 其他根据系统设置

     **/

    public function initialize()

    {

        $action = [

            'permission' => ['stafflistfen','setshoplistlianying','setntclientlistinfo','setcustomerinfolist','swipecustomerdata','setscarcustomerlisty','setcustomeralist','setcustomerinfopingci','setfieveightcustomer','setthreeclientinfos','getzgshopcustomerlist','updateservicelistdc','setcustomerlistdc','setupdateclientmoneylist','setcustomercaselist','grouplistfenzhi','setmenzhenclients','setmonthshujudaochulist','updatecustomerlist','setthreenuriceshopamount','setdshiqi','setproductlist','setdongbeicustomer','qkcustomeryucun','setbandxmamount','staffservicelist','setshopamounttj','setcustomercjnum','setcustomerblance','sendeccomeinfo','clientvipbirth','clientyucun','checkclientyue','gongxiaomanyi','setservicecardadd','setceshiclient','updateservicecard','daochuclientinfo','delclientyucun','clientlistinfo','monthshujudaochu','setscarlist','setclientlistu','sendvipclient','exportcustomerdataonmonth'],

            'allow' => ['users', 'info']

        ];

        event('CheckAuth',$action);

        $request = $this->request;

        $a = strtolower($request->action());



        $this->customerModel = new CustomerModel();

        $this->customerConfigModel = new CustomerConfigModel();

        if (!in_array($a, $action['permission'])) {

            parent::initialize();

        }

    }




    /**
     * 员工分值导出
     */
    public function stafflistfen()
    {

		$starttime =strtotime($_GET['getstarttime']);
		$endtime=strtotime($_GET['getendtime'])+86399;

		$where  = [];

		array_push($where,['status','=',1]);

		$personList = Db::name('shop_person')->where($where)->column('shop_id,shop_name,name,id');


		foreach ($personList as $key => $val)
		{

			//预约客户评价--分值
			$serviceCount = Db::name('service_log')

									->where('nurse_id_one','=',$val['id'])

									->where('create_time','>=',$starttime)

									->where('create_time','<=',$endtime)

									->where('comment_time','<>',0)

									->column('id,avg(fraction)');


			//新单顾客回访--分值
			$clientAvgTotal = Db::name('return_vist_t')

									->where('nurse_id','=',$val['id'])

									->where('create_time','>=',$starttime)

									->where('create_time','<=',$endtime)

									->where('total','<>',0)

									->column('nurse_id,avg(total)');


			$yueyuepingfen=isset($serviceCount[0]['avg(fraction)'])? ceil($serviceCount[0]['avg(fraction)'] * 20): '';

			$huifangpingfen=isset($clientAvgTotal[0]['avg(total)'])? ceil($clientAvgTotal[0]['avg(total)']): '';


			// 服务评价 有分值 新单评价 有分值
			if($yueyuepingfen!='' && $huifangpingfen!=''){

				$avgfent = ($yueyuepingfen+ $huifangpingfen)/2 ;
			}
			// 服务评价 有分值 新单评价 无分值
			if($yueyuepingfen!='' && $huifangpingfen ==''){

				$avgfent= $yueyuepingfen;
			}
			// 服务评价 无分值 新单评价 有分值
			if($yueyuepingfen =='' && $huifangpingfen !=''){

				$avgfent = $huifangpingfen;
			}

			// 服务评价 无分值 新单评价 无分值
			if($yueyuepingfen =='' && $huifangpingfen ==''){

				$avgfent= '';
			}


			$personList[$key]['yueyuepingfen'] = $yueyuepingfen;

			$personList[$key]['huifangpingfen'] = $huifangpingfen;

			$personList[$key]['zonghedefen'] = $avgfent;

		}

		$callName = [

		            'shop_name'        =>'门店',

		            'name'             =>'姓名',

		            'yueyuepingfen'    =>'服务评分',

		            'huifangpingfen'   =>'回访评分',

		            'zonghedefen'      =>'综合得分'

		        ];

		$downUrl = exportExcelData('员工评分导出',$callName,$personList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


    }


	/**
	 * 店面分值导出
	 */


	public function grouplistfenzhi()
	{

		$starttime =strtotime($_GET['getstarttime']);
		$endtime=strtotime($_GET['getendtime'])+86399;
		//成交开始时间 和 成交结束时间
		$cjstarttime=strtotime(date("Y-m-01 00:00:00",strtotime("-4 month",$starttime)));// 新单成交  开始时间
		$cjendtime=strtotime(date('Y-m-01', $cjstarttime) . ' 1 month -1 day'. ' 23:59:59');//新单成交 成交结束时间

		//保有开始时间 和 保有结束时间
		$bystarttime=strtotime(date("Y-m-01 00:00:00",strtotime("-1 month",$starttime)));// 查询开始时间
		$byendtime   = strtotime($_GET['getendtime'])+86399;    // 3月31日

		$where  = [];

		array_push($where,['type','=',1]);

		array_push($where,['shop_id','<>',74076159037951]);

		//array_push($where,['shop_id','=',73782774834649]);//

		$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');

		$caseArray =[2,3];


		$numtotal = 0;
		foreach ($shopList as $key => $val)
		{
			//新单顾客回访--分值
			$groupAvgTotal = Db::name('return_vist_t')

							->where('shop_id','=',$val['shop_id'])

							->where('create_time','>=',$starttime)

							->where('create_time','<=',$endtime)

							->where('total','<>',0)

							->column('shop_id,avg(total)');


			//预约客户评价--分值
			$serviceCount = Db::name('service_log')

							->where('shop_id','=',$val['shop_id'])

							->where('create_time','>=',$starttime)

							->where('create_time','<=',$endtime)

							->where('comment_time','<>',0)

							->column('shop_id,avg(fraction)');

			$yueyuepingfen=isset($serviceCount[0]['avg(fraction)'])? ceil($serviceCount[0]['avg(fraction)'] * 20): '';

			$huifangpingfen=isset($groupAvgTotal[0]['avg(total)'])? ceil($groupAvgTotal[0]['avg(total)']): '';


			// 服务评价 有分值 新单评价 有分值
			if($yueyuepingfen!='' && $huifangpingfen!=''){

				$avgfent = ($yueyuepingfen+ $huifangpingfen)/2 ;
			}
			// 服务评价 有分值 新单评价 无分值
			if($yueyuepingfen!='' && $huifangpingfen ==''){

				$avgfent= $yueyuepingfen;
			}
			// 服务评价 无分值 新单评价 有分值
			if($yueyuepingfen =='' && $huifangpingfen !=''){

				$avgfent = $huifangpingfen;
			}

			// 服务评价 无分值 新单评价 无分值
			if($yueyuepingfen =='' && $huifangpingfen ==''){

				$avgfent= '0';
			}

			//店面保有率客户数据统计；  查询12月成交的新单顾客在 2-3月是否有 服务记录 是否有 订单 或者 优案

			//查询 成交月份总的成交数
			$cjlistotal = Db::name("customer")

						->where('cj_time','>=',$cjstarttime)

						->where('cj_time','<=',$cjendtime)

						->column('shop_id,count(id)');

			//成交月份总的成交个数
			$zcjcount = $cjlistotal[0]['count(id)'];

			//每个门店的成交数
			$cjlist = Db::name("customer")

					->where('shop_id','=',$val['shop_id'])

					->where('cj_time','>=',$cjstarttime)

					->where('cj_time','<=',$cjendtime)

					->column('id,cj_money,shop_id');

			//查询保有人数

			$numb = 0;

			foreach ($cjlist as $k1 => $v1)
			{
				//查询服务核销记录信息
				$servicelog_one = Db::name('service_log')

								->where('customer_id','=',$v1['id'])

								->where('create_time','>=',$bystarttime)

								->where('create_time','<=',$byendtime)

								->column('id');

				//查询案例记录信息
				$caselist_two = Db::name('case')

								->where('customer_id','=',$v1['id'])

								->where('tg_excellent_time','>=',$bystarttime)

								->where('tg_excellent_time','<=',$byendtime)

								->where('selection_results','in',$caseArray)

								->column('id');


				//查询销售单信息
				$shopOrderist_three = Db::name('shop_order')

								->where('uid','=',$v1['id'])

								->where('deal_time','>=',$bystarttime)

								->where('deal_time','<=',$byendtime)

								->where('status','<>',4)

								->column('uid');


				//查询收款单/退款单信息
				$tuiklist_four = Db::name('shop_refund_order')

								->where('uid','=',$v1['id'])

								->where('deal_time','>=',$bystarttime)

								->where('deal_time','<=',$byendtime)

								->where('status','<>',4)

								->column('uid');

				if(empty($servicelog_one) && empty($caselist_two) && empty($shopOrderist_three) && empty($tuiklist_four))
				{
					//流失顾客统计
					$numb++;

				}else{

					//保有客户总计
					$numtotal++;
				}
			}


			$shopList[$key]['lscount'] = $numb;

			$shopList[$key]['bycount'] = count($cjlist)-$numb;

			$shopList[$key]['cjcount'] = count($cjlist);

			$shopList[$key]['bylv'] = count($cjlist) > 0 ? bcdiv((count($cjlist)-$numb),count($cjlist),2) : '0'; // 保有率


			$shopList[$key]['laogukefenzhi'] = $avgfent;

			//$shopList[$key]['yuepingfeny'] = $yueyuepingfen;

			//$shopList[$key]['huifpingfeny'] = $huifangpingfen;

		}

		//保有率平均值：
		$avgbylv = bcdiv($numtotal,$zcjcount,2)  ;
		echo '保有率平均值'.$avgbylv;echo '<br/>';

		/* echo '<pre>';
		print_r($shopList); */

		foreach ($shopList as $kd => $vll2)
		{

			if($vll2['bylv']< $avgbylv){
				$zhbyfenzhi = $vll2['bylv'] ='0.00' ? bcdiv($vll2['bylv'],$avgbylv,2) *100 :0;

			}else{
				$zhbyfenzhi ='100';
			}

			//echo $vll2['shop_name'].$zhbyfenzhi;echo '<br/>';
			$shopList[$kd]['BYT'] = $zhbyfenzhi;
			// 老顾客 avgfent 有分值 保有率  zhbyfenzhi 有分值
			if($vll2['laogukefenzhi']!='0.00' && $zhbyfenzhi !='0'){

				$avgbyfent1 = bcadd(($vll2['laogukefenzhi'] * 0.8),($zhbyfenzhi * 0.2),2);

			}
			// 老顾客 有分值 保有率 无分值
			if($vll2['laogukefenzhi']!='0.00' && $zhbyfenzhi == '0'){

				$avgbyfent1= $vll2['laogukefenzhi'];
			}
			// 老顾客 无分值 保有率 有分值
			if($vll2['laogukefenzhi'] =='0.00' && $zhbyfenzhi !='0'){

				$avgbyfent1 = $zhbyfenzhi;
			}

			// 老顾客 无分值 保有率 无分值
			if($vll2['laogukefenzhi'] =='0.00' && $zhbyfenzhi =='0'){
				$avgbyfent1= '';
			}
			$shopList[$kd]['zuihouby'] = $zhbyfenzhi;
			$shopList[$kd]['zuihoudefen'] = $avgbyfent1;


		}
		/* echo '<pre>';
		print_r($shopList); */
		$callName = [

		            'market_name'        =>'市场',

		            'shop_name'          =>'门店',

		            'laogukefenzhi'        =>'老顾客分值',

		            'zuihouby'           =>'保有分值',

		            'zuihoudefen'        =>'综合得分'

		        ];

		$downUrl = exportExcelData('店面评分导出',$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";



	}
	/**
	 * 店面客户分类修改
	 */

	public function staffservicelist()
	{

		$startTime='1646067661';  //2022-03-01
		$endTime='1648483199';  //2022-03-28

		$where  = [];

		array_push($where,['type','=',1]);

		array_push($where,['shop_id','<>',74076159037951]);

		$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');

		foreach ($shopList as $key => $val)
		{
			//每个门店的成交数
			$cjlist = Db::name("customer")

					->where('shop_id','=',$val['shop_id'])

					->where('cj_time','>=',$startTime)

					->where('cj_time','<=',$endTime)

					->column('count(id),shop_id');


			$shopList[$key]['countcj'] = $cjlist[0]['count(id)'];


		}

		$callName = [

		            'market_name'        =>'市场',

		            'shop_name'          =>'门店',

		            'countcj'        =>'成交个数'

		        ];

		$downUrl = exportExcelData('店面评分导出',$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}

	//会员生日导出
	public function clientvipbirth()
	{
		//echo '会员生日导入';

		//$birMonth = $_GET['month'];

		//membership_leve
		$where  = [];

		$daytime = time();

		array_push($where,['membership_leve','>=',1]);
		array_push($where,['be_overdue_time','>=',$daytime]);
		array_push($where,['birthday','>=',0]);
		//array_push($where,['shop_id','=',73782775030746]);

		$customerList = Db::name('customer')->where($where)->column('id,shop_id,nurse_id,customer_name,birthday,telephone,be_overdue_time,membership_leve,consumption');

		$shop_personList = Db::name('shop_person')->column('name','id');

		//获取会员等级
		$gradeList      = Db::name('customer_grade')->column('name', 'id');
		$huiyuan_chadianguoqi_time = 24*60*60*30;


		foreach ($customerList as $key => $val)
		{

			//$birth=$val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '未进行一次进店';

			$shopMarket = Db::name('shop')->where('shop_id','=',$val['shop_id'])->column('id,shop_name,market_name');



			$customerList[$key]['shop_name'] = $shopMarket[0]['shop_name'];
			$customerList[$key]['market_name'] = $shopMarket[0]['market_name'];
			if($val['birthday']!=0){
				$customerList[$key]['birth'] = date('m-d', $val['birthday']);
				$customerList[$key]['month'] = date('m', $val['birthday']);
			}else{
				$customerList[$key]['birth'] = '';
				$customerList[$key]['month'] = '';
			}

			$customerList[$key]['nurse_name'] = $shop_personList[$val['nurse_id']] ?? '';
			$customerList[$key]['grade_name'] = $gradeList[$val['membership_leve']] ?? '';

			$customerList[$key]['dq_time'] = date('Y-m-d', $val['be_overdue_time']);

			if ($val['be_overdue_time'] > 0){
				if($val['be_overdue_time'] >time()){
					$customerList[$key]['huiyuan_status'] = '生效中';
					if(($val['be_overdue_time']-$huiyuan_chadianguoqi_time) <=time()){
						$customerList[$key]['huiyuan_status'] = '即将过期';

					}
				}else{
					$customerList[$key]['huiyuan_status'] = '已过期';
				}
			}else{
				$customerList[$key]['huiyuan_status'] = '非会员';
			}
		}
		// echo '<pre>';
		// print_r($customerList);exit;
		$callName = [

		            'market_name'        =>'市场',

		            'shop_name'          =>'门店',

		            'nurse_name'        =>'护理师',

					'customer_name'        =>'姓名',

					'telephone'        =>'手机号',

					'birth'        =>'生日',

					'grade_name'        =>'会员等级',

					'huiyuan_status'        =>'状态',

					'month'        =>'月份',
					
					'dq_time'        =>'到期时间',
					
					'consumption'        =>'累计金额'

		        ];

		$downUrl = exportExcelData('会员生日数据',$callName,$customerList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


	}

	/*
	@ 导出中台系统中 消费金额大于10 w 且优案毕业的
	*/

	public function setcustomerblance(){

		//查询案例记录信息

		//$where = [];

		$caseArray =[2,3];

		//array_push($where,['graduation_type','=',1]);

		$caselist_two = Db::name('case')

						//->where('graduation_type','>',0)

						->where('selection_results','in',$caseArray)

						->column('id,shop_id,customer_id,position_name,reason_name,type_name,selection_results,graduation_type');

		$ids = array_values(array_unique(array_column($caselist_two,'customer_id')));
		/* echo '<pre>';
		print_r($caselist_two);exit; */
		/* echo '<pre>';
		print_r($ids);exit; */

		$where = [];

		array_push($where,['id','in',$ids]);

		$customers = Db::name('customer')->where($where)->column('customer_name,telephone,sex,age,consumption,create_time','id');
		/* echo '<pre>';
		print_r($customers);//exit; */
		$sexArr= ['0' => '未知', '1' => '男', '2' => '女'];

		$caseArr= [ '2' => '优秀案例', '3' => '满意案例'];


		$wheres = [];

		//array_push($wheres,['shop_id','in',$shop_ids]);
		array_push($wheres,['type','=',1]);
		$shopList = Db::name('shop')->where($wheres)->column('shop_name','shop_id');
		/* echo '<pre>';
		print_r($shopList);exit; */


		$list =[];

		$k = 0;

		foreach ($caselist_two as $key => $val){


			$one = [];

			$one['customer_name'] = $customers[$val['customer_id']]['customer_name'] ?? '';
			$one['telephone'] = $customers[$val['customer_id']]['telephone'] ?? '';
			$one['sex'] = $sexArr [$customers[$val['customer_id']]['sex'] ?? 0] ;
			$one['age'] = $customers[$val['customer_id']]['age'] ?? '';

			$one['position_name'] = $val['position_name'] ?? '';
			$one['reason_name'] = $val['reason_name'] ?? '';
			//$one['type_name'] = $val['type_name'] ?? '';
			$one['consumption'] = $customers[$val['customer_id']]['consumption'] ?? '';
			$one['create_time'] = date('Y-m-d',$customers[$val['customer_id']]['create_time'] ?? '0' ) ;

			$one['selection_results'] = $caseArr[$val['selection_results'] ?? ''];
			$one['graduation_type'] = $val['graduation_type'] ?? '';

			$one['shop_name'] = $shopList[$val['shop_id']] ?? '';



			array_push($list,$one);
			//$k++;
		}

		/* echo '<pre>';
		print_r($list);exit; */

		$callName = [

		            'customer_name'        =>'姓名',

		            'telephone'          =>'电话',

		            'sex'        =>'性别',

					'age'        =>'年龄',

					'shop_name'        =>'门店',

					'position_name'        =>'部位',

					'reason_name'        =>'疤痕类型',

					//'type_name'        =>'疤痕原因',

					'consumption'        =>'消费金额',

					'create_time'        =>'录入时间',

					'selection_results'        =>'案例类型',

					'graduation_type'        =>'是否毕业'

		        ];

		$downUrl = exportExcelData('优案毕业客户',$callName,$list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


		exit;

	}



	//  调整客户预存
	public function clientyucun(){

		//echo '55555';exit;
		if (empty($_FILES['file'])) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/kehuyucuntiaozheng.html">返回【客户预存基本信息导入】页面</a>';exit;

		}

		$tmp_name = $_FILES['file']['tmp_name'];

		if (empty($tmp_name) || !file_exists($tmp_name)) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/kehuyucuntiaozheng.html">返回【客户预存基本信息导入】页面</a>';exit;

		}


		$importData = readExcelData($tmp_name);

		$importData = array_values($importData);
		echo count($importData); //exit;
		// echo '<pre>';
		// print_r($importData);exit;
		
		foreach ($importData as $key => $val){
			
			$where = [];
			
			array_push($where,['telephone','=',$val[2]]);
			array_push($where,['shop_id','=',79154893551569]);
			
			$customer_list = Db::name('customer')->where($where)
			// ->count();
			// ->limit(10000,5000)
			->column('id,customer_name,telephone,fcustId,king_no');
			
			
			echo '<pre>';
			print_r($customer_list);
			
			
			if(empty($customer_list)){
				echo '数据是空的 不需要修改----'.$val[2];
			}else{
				
				foreach ($customer_list as $k => $v){
					
					
					$saves=[
					
						 'fcustId'=>$val[1],
						 'king_no'=>$val[0],
					
						];
					
					//$res = Db::name('customer')->where('id','=',$v['id'])->update($saves);
					
					echo '修改成功--'.$res ;echo '<br/>';
				}
			}
			
			
			
			
		}
		
		
		
		
		
		
		/* 更新客户K3代码 和内码信息 */
		exit;
		
		
		$num = 0;
		foreach ($importData as $key => $val){

			//$customerBlance = Db::name("customer_balance")->where('customer_id','=',$val[0])->column('id,shop_id,customer_id,balance_category_id,money,complimentary_money');
			//$customerList = Db::name("customer")->where('telephone','=',$val[1])->column('id,telephone,shop_id,consumption,membership_leve,member_classification');
			
			$customerList= Db::name('customer')->alias('c')
			
			            ->join('shop s', 'c.shop_id=s.shop_id', 'LEFT')
			
						->where('c.telephone','=',$val[1])
			
						->where('s.shop_type','=',2)
			
						->column('c.id,c.shop_id,c.telephone,c.consumption,c.membership_leve,c.member_classification,c.create_time');
			

			
			
			//echo $customerList[0]['consumption'];echo '<br/>';
			
			foreach ($customerList as $k => $v){
				
				if($v['consumption'] == $val[2]){
					
					//echo '客户累计消费金额数据相同'.$val[2];echo '<br/>';
					
				}else{
					//echo '客户累计消费金额数据不相同8888==='.$val[2];echo '<br/>';
					
					
					if($v['consumption'] > $val[2]){
						
					}else{
						
						
						if($v['create_time'] < '1646064000'){
							$num ++;
							
							if($val[2]>= 50000){
								$aa = 73;
							}
							
							if($val[2]>=15000 && $val[2]< 50000){
								$aa = 72;
							}
							
							if($val[2]>=5000 && $val[2]< 15000){
								$aa = 71;
							}
							
							if($val[2]>0 && $val[2]< 5000){
								$aa = 70;
							}
							
							
							$saveTJ=[];
							$saveTJ=[
								
								 'consumption'=>$val[2],
								 
								 'member_classification'=>$aa,
								 
								 //'id'=>$v['id'],
							
								];
							
							// Db::name('customer')->where('id','=',$v['id'])->update($saveTJ);
								
							// echo '<pre>';
							// print_r($saveTJ);
						}else{
							
							
						}
						
							
							
					}
					
					
					
					
					
					//Db::name('customer_balance')->where('id','=',$v['id'])->update($saveTJ);
					
					
					
				}
				
			}
			
			
		}
		echo $num ;



















		exit;















		//客户内码数据
		$inWheref = array_values(array_unique(array_column($importData,'2')));

		$cunzaiShoujihao = Db::name("customer")->where('king_no','in',$inWheref)->column('id,shop_id,fcustId,king_no');
		$shopID = '73782775159891';  //北市店
		/* echo '<pre>';
		print_r($cunzaiShoujihao);
		echo count($cunzaiShoujihao);exit; */
		//修改中台系统中的 客户余额 2022-03-19
		foreach ($importData as $key => $val){

			$customerId = Db::name("customer")->where('king_no','=',$val[2])->column('id,shop_id');

			if(empty($customerId)){
				echo '客户内码+ ：'.$val[2];echo '<br/>';
			}else{
				//更新疤痕 本金和赠金预存
				if($val[4]!='' || $val[5]!=''){

					if($val[4]!=''){
						$benmoney = $val[4];
					}else{
						$benmoney = 0.00;
					}

					if($val[5]!=''){
						$zengmoney = $val[5];
					}else{
						$zengmoney = 0.00;
					}

					$customerBlance = Db::name("customer_balance")->where('customer_id','=',$customerId[0]['id'])->column('id,shop_id,balance_category_id');

					if(!empty($customerBlance)){
						//echo '不疤痕4474745 ---- '.$customerId[0]['id'].'--';echo '<br/>';
						$save = [
						           1=>[

						                'shop_id'     => $shopID,
						                'customer_id' => $customerId[0]['id'],
						                'balance_category_id' => 1,
						                'money' => $benmoney,
						                'complimentary_money' => $zengmoney,
						            ],

						           2=>[
						                'shop_id'     => $shopID,
						                'customer_id' => $customerId[0]['id'],
						                'balance_category_id' => 2,
						                'money' => 0.00,
						                'complimentary_money' => 0.00,
						            ]
						        ];

							/* echo '<pre>';
							print_r($save); */
							foreach ($customerBlance as $k => $v){
								//echo '2252';
							   $res = Db::name('customer_balance')->where('id','=',$v['id'])->update($save[$v['balance_category_id']]);

							}

					}else{
						//echo '新增疤痕-252525-'.$customerId[0]['id'].'--';echo '<br/>';
						$cbadd = [
									[

										'shop_id'     => $shopID,
										'customer_id' => $customerId[0]['id'],
										'balance_category_id' => 1,
										'money' => $benmoney,
										'complimentary_money' => $zengmoney,
									],

									[
										'shop_id'     => $shopID,
										'customer_id' => $customerId[0]['id'],
										'balance_category_id' => 2,
										'money' => 0.00,
										'complimentary_money' => 0.00,
									]
								];

						Db::name('customer_balance')->insertAll($cbadd);
					}

				}
				//更新项目 本金和赠金预存
				if($val[6]!='' || $val[7]!=''){

					if($val[6]!=''){
						$xbenmoney = $val[6];
					}else{
						$xbenmoney = 0.00;
					}

					if($val[7]!=''){
						$xzengmoney = $val[7];
					}else{
						$xzengmoney = 0.00;
					}

					$customerBlance = Db::name("customer_balance")->where('customer_id','=',$customerId[0]['id'])->column('id,shop_id,balance_category_id');

					if(!empty($customerBlance)){
						//echo '不项目225252 ---- '.$customerId[0]['id'].'--';echo '<br/>';
						$save = [
						           1=>[

						                'shop_id'     => $shopID,
						                'customer_id' => $customerId[0]['id'],
						                'balance_category_id' => 1,
						                'money' => 0.00,
						                'complimentary_money' => 0.00,
						            ],

						           2=>[
						                'shop_id'     => $shopID,
						                'customer_id' => $customerId[0]['id'],
						                'balance_category_id' => 2,
						                'money' => $xbenmoney,
						                'complimentary_money' => $xzengmoney,
						            ]
						        ];

							/* echo '<pre>';
							print_r($save); */
							foreach ($customerBlance as $k => $v){
								//echo '66666';
							   $res = Db::name('customer_balance')->where('id','=',$v['id'])->update($save[$v['balance_category_id']]);

							}

					}else{
						//echo '新项目55454545222'.$customerId[0]['id'].'--';echo '<br/>';
						$cbadd = [
									[

										'shop_id'     => $shopID,
										'customer_id' => $customerId[0]['id'],
										'balance_category_id' => 1,
										'money' => 0.00,
										'complimentary_money' => 0.00,
									],

									[
										'shop_id'     => $shopID,
										'customer_id' => $customerId[0]['id'],
										'balance_category_id' => 2,
										'money' => $xbenmoney,
										'complimentary_money' => $xzengmoney,
									]
								];

						Db::name('customer_balance')->insertAll($cbadd);
					}



				}




			}


		}



	}


	//客户预存导出
	public function checkclientyue(){

		$shopWhere = [];

		//array_push($shopWhere,['type','=',1]);

		//array_push($shopWhere,['market_id','in',[6,3,7]]);  73782774792133
		array_push($shopWhere,['shop_id','=',73782775206631]);  //西安门诊

		$zyShopList = Db::name('shop')->where($shopWhere)->column('shop_id,shop_name','shop_id');
		/* echo '<pre>';
		print_r($zyShopList); */

		$shopIds = array_values(array_unique(array_column($zyShopList,'shop_id')));

		$clientList = Db::name('customer_balance')

					->where('shop_id','in',$shopIds)

					//->where('money','<>',0)

					//->whereOr('complimentary_money','<>',0)

					->column('customer_id,balance_category_id,money,complimentary_money,shop_id');


		echo count($clientList);
		/* echo '<pre>';
		print_r($clientList);
		exit; */
		$clientyue = [];
		$k=0;
		foreach ($clientList as $key => $val){

			$customerName = Db::name("customer")->where('id','=',$val['customer_id'])->column('customer_name,telephone,fcustId,king_no,id');
			/* echo '<pre>';
			print_r($customerName); */
			if(empty($customerName)){

			}else{

				$clientyue [$k]['customer_id'] = $val['customer_id'];

				$clientyue [$k]['customername'] = $customerName[0]['customer_name'];

				$clientyue [$k]['telephone'] = $customerName[0]['telephone'];

				$clientyue [$k]['fcustId'] = $customerName[0]['fcustId'];

				$clientyue [$k]['king_no'] = $customerName[0]['king_no'];

				$clientyue [$k]['balance_category_id'] = $val['balance_category_id'];

				$clientyue [$k]['money'] = $val['money'];
				$clientyue [$k]['complimentary_money'] = $val['complimentary_money'];
				$clientyue [$k]['shop_name'] = $zyShopList[$val['shop_id']]['shop_name'];

				$k++;
			}



		}
		/* echo '<pre>';
		print_r($clientyue);exit; */


		$callName = [

		            'customer_id'         =>'ID',

		            'customername'        =>'姓名',

					'telephone'           =>'手机号',

					'shop_name'           =>'门店',

		            'fcustId'             =>'客户内码',

		            'king_no'             =>'客户K3代码',

		            'balance_category_id'    =>'类型',

		            'money'                  =>'本金',

		            'complimentary_money'      =>'赠金',

		        ];

		$downUrl = exportExcelData('客户预存明细',$callName,$clientyue,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}


	//导出12月成交的客户档案 ---在12月--3月之间的功效满意度

	public function gongxiaomanyi(){

		//echo '功效满意度';

		$cjstarttime = '1638291661';
		$cjendtime = '1640966399';

		$cjendtimett = '1648742399';

		$where  = [];

		array_push($where,['type','=',1]);

		array_push($where,['shop_id','<>',74076159037951]);


		$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');
		$caseArray =[2,3];
		$numtotal = 0;

		foreach ($shopList as $key => $val)
		{

			//每个门店的成交数
			$cjlist = Db::name("customer")

					->where('shop_id','=',$val['shop_id'])

					->where('cj_time','>=',$cjstarttime)

					->where('cj_time','<=',$cjendtime)

					->column('id,cj_money,shop_id');
			 $numtotal = count($cjlist);

			/* echo '<pre>';
			print_r($cjlist); */

			if(empty($cjlist)){
				$shopList[$key]['zshu_t'] = 0;

				$shopList[$key]['liushi'] = 0;

				$shopList[$key]['baoyou'] = 0;
			}else{
				$numls = 0;
				$numls11 = 0;
				foreach ($cjlist as $k1 => $v1){
					//查询是否
					$clientamount1 = Db::name("shop_refund_order")

							->where('uid','=',$v1['id'])

							->where('deal_time','>=',$cjstarttime)

							->where('deal_time','<=',$cjendtimett)

							->where('shop_type','=',2)

							->where('refund_type','<>',101.03)

							->where('type','=',3)

							->column('uid,sum(pay_amount),shop_id');

					$clientamount2 = Db::name("shop_refund_order")

							->where('uid','=',$v1['id'])

							->where('deal_time','>=',$cjstarttime)

							->where('deal_time','<=',$cjendtimett)

							->where('shop_type','=',2)

							->where('refund_type','<>',101.03)

							->where('type','=',1)

							->column('uid,sum(pay_amount),shop_id');

					$amountleij = $clientamount1[0]['sum(pay_amount)']-$clientamount2[0]['sum(pay_amount)'];

					//查询案例记录信息
					$caselist_two = Db::name('case')

									->where('customer_id','=',$v1['id'])

									->where('tg_excellent_time','>=',$cjstarttime)

									->where('tg_excellent_time','<=',$cjendtimett)

									->where('selection_results','in',$caseArray)

									->column('id');



					if($amountleij < 500 && empty($caselist_two)){
						$numls++;
					}else{
						$numls11++;
					}


				}

				$shopList[$key]['zshu_t'] = $numtotal;

				$shopList[$key]['liushi'] = $numls;

				$shopList[$key]['baoyou'] = $numls11;

			}



		}


		$callName = [

		            'shop_name'         =>'门店',

		            'market_name'        =>'市场',

					'zshu_t'           =>'成交总数',

					'baoyou'           =>'满意数',

		        ];

		$downUrl = exportExcelData('功效满意度',$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}

	//根据客户ID清空客户的 预存
	public function delclientyucun(){

		if (empty($_FILES['file'])) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/delclientyucun.html">返回【客户预存清零导入】页面</a>';exit;

		}

		$tmp_name = $_FILES['file']['tmp_name'];

		if (empty($tmp_name) || !file_exists($tmp_name)) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/delclientyucun.html">返回【客户预存清零导入】页面</a>';exit;

		}


		$importData = readExcelData($tmp_name);

		$importData = array_values($importData);
		/* echo '<pre>';
		print_r($importData); exit; */

		$shopID = 73782775241316;
		foreach ($importData as $key => $val)
		{

			//根据客户--编码和内码进行查询
			$customerBlance= Db::name('customer')->alias('c')

			            ->join('customer_balance cb', 'cb.customer_id=c.id', 'LEFT')

						->where('c.fcustId','=',$val[3])

						->where('c.shop_id','=',$shopID)

						->column('cb.id,cb.shop_id,cb.customer_id,cb.balance_category_id');

			/* $customerBlance = Db::name('customer_balance')

						->where('customer_id','=',$val[0])

						//->where('money','<>',0)

						//->whereOr('complimentary_money','<>',0)

						->column('customer_id,balance_category_id,money,complimentary_money,shop_id'); */

				echo '<pre>';
				print_r($customerBlance); //exit;
			//根据客户--ID进行查询


			if(empty($customerBlance)){
				echo '客户手机号+ ：'.$val[0].'++'.$shopID;echo '<br/>';
			}else{

				$save = [
						   1=>[

								'shop_id'     => $shopID,
								//'customer_id' => $val[0],
								'balance_category_id' => 1,
								'money' => 0.00,
								'complimentary_money' => 0.00,
							],

						   2=>[
								'shop_id'     => $shopID,
								//'customer_id' => $val[0],
								'balance_category_id' => 2,
								'money' => 0.00,
								'complimentary_money' => 0.00,
							]
				];

				foreach ($customerBlance as $k => $v){

					$saveTJ=[];
					$saveTJ=[

					     'money'=>0,
						 'complimentary_money'=>0,

					    ];

				   //Db::name('customer_balance')->where('id','=',$v['id'])->update($saveTJ);

				}

			}
			//exit;


			/* //根据客户--编码和内码进行查询
			$customerBlanceTJ= Db::name('customer')->alias('c')

			            ->join('customer_balance_tj cb', 'cb.customer_id=c.id', 'LEFT')

						->where('c.telephone','=',$val[0])

						->where('c.shop_id','=',$shopID)

						->column('cb.id,cb.shop_id,cb.customer_id,cb.balance_category_id');



			echo '<pre>';
			print_r($customerBlanceTJ);
			foreach ($customerBlanceTJ as $k21 => $v21){

				$save=[];
				$save=[

				     'money'=>0,
					 'complimentary_money'=>0,

				    ];

			    Db::name('customer_balance_tj')->where('id','=',$v21['id'])->update($save);

			} */

		}



	}
	// 财务每月导出的网络档案数据报表
	public function clientlistinfo(){

		$startTime  = $_GET['start_time'];
		$endTime  = $_GET['end_time'];

		$start_time_int = strtotime($startTime);
		$end_time_int = strtotime($endTime)+86399;

		$where = [];
		$wheres =[];
		$xianshang = [43,44,118,50,51,52,54,55,56,53,584];
		array_push($where,['source_id','in',$xianshang]);
		array_push($where,['create_time','>=',$start_time_int]);
		array_push($where,['create_time','<=',$end_time_int]);
		array_push($wheres,['type','=',1]);
		array_push($wheres,['status','=',1]);

		$shopList = Db::name('shop')->where($wheres)->column('shop_name,shop_id');
		foreach($shopList as $key => $val){

			$customerList = Db::name('customer')->where($where)->where('shop_id','=',$val['shop_id'])->column('count(id) as idnum,shop_id');
			$shopList[$key]['count']=$customerList[0]['idnum'];
		}

		$callName = [

					'shop_id'   =>'门店ID',
					'shop_name' =>'门店',
					'count' =>'档案',
				];

		$downUrl = exportExcelData('客户档案'.$startTime,$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";
	}


	public function setscarlist(){

		$startTime  = $_GET['start_time'];
		$endTime  = $_GET['end_time'];

		$start_time_int = strtotime($startTime);
		$end_time_int = strtotime($endTime)+86399;

		$scarList = Db::name('customer')
		    ->alias('b')
		    ->where('b.cj_time','>=',$start_time_int)//2022.01.01
		    ->where('b.cj_time','<=',$end_time_int) //2022.5.31
		    ->where('b.cj_money','>',0)
		    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
		    //->where('sp.business_type','=',1)
		    ->where('sp.type','=',3)
			//->where('sp.shop_id','=',$value['shopId'])
		    ->column('b.id,b.shop_id,b.customer_name,b.sex,b.age,b.birthday,b.source_id,b.membership_leve,b.telephone,b.repair_progress,b.cj_money,b.cj_time,sp.business_type,b.nurse_id,b.consumption');

		$sexArr= ['0' => '未知', '1' => '男', '2' => '女'];
		$scarArr= ['0' => '未知', '1' => '疤痕', '2' => '项目'];
		//获取会员等级
		$gradeList= Db::name('customer_grade')->column('name', 'id');
		//基础配置
		$settingList    = Db::name('setting')->column('name', 'id');


		foreach($scarList as $key => $val){

			$where  = [];

			array_push($where,['shop_id','=',$val['shop_id']]);
			$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');

			$scarList[$key]['shop_name'] = $shopList[0]['shop_name'];
			$scarList[$key]['market_name'] = $shopList[0]['market_name'];
			$scarList[$key]['cj_timet'] = $val['cj_time'] ? date('Y-m-d',$val['cj_time']) : '' ;

			if($val['nurse_id']==0){
				$scarList[$key]['hulishi']='';
			}else{
				$shop_personList = Db::name('shop_person')->where('id','=',$val['nurse_id'])->column('name,id');
				$scarList[$key]['hulishi']=$shop_personList[0]['name'];
			}

			$scarList[$key]['sex'] = $sexArr[$val['sex']] ?? '未填写';
			$scarList[$key]['business_type'] = $scarArr[$val['business_type']] ?? '未填写';
			$scarList[$key]['birthday']        = $val['birthday'] ? date('Y-m-d', $val['birthday']) : '';
			$scarList[$key]['source_name']     = $settingList[$val['source_id']] ?? '';
			$scarList[$key]['grade_name']      = $gradeList[$val['membership_leve']] ?? '';

			$scarList[$key]['progress_name']   = $settingList[$val['repair_progress']] ?? '';

			$scarListY    = Db::name('customer_scar')
			             ->where('customer_id','=',$val['id'])
			             ->group('customer_setting_id_type')
			             ->select()->toArray();
			$scar_aggregate          = '';
			if ($scarListY){
			    $typeIdArr           = array_column($scarListY, 'customer_setting_id_type');
			    foreach ($typeIdArr as $typeK => $typeV){
			        $str = $settingList[$typeV] ?? '';
			        $scar_aggregate .= $str.',';
			    }
			}
			$scarList[$key]['scar_aggregate']        = $scar_aggregate;

		}
		/* echo '<pre>';
		print_r($scarList);exit; */

		$callName = [
					'shop_name' =>'门店',
					'market_name' =>'市场',
					'cj_timet'   =>'成交时间',
					'customer_name' =>'客户姓名',
					'telephone' =>'电话',
					'sex' =>'性别',
					'age' =>'年龄',
					'birthday' =>'生日',
					'source_name' =>'来源',
					'grade_name' =>'会员等级',
					'hulishi' =>'护理师',
					'progress_name' =>'修复进展',
					'scar_aggregate' =>'疤痕类型',
					'consumption' =>'消费金额',
					'cj_money' =>'成交金额',
					'business_type' =>'成交类型',
				];


		/* $callName = [

					'customer_name'   =>'客户姓名',
					'telephone' =>'手机号',
					'cj_money' =>'成交金额',
					'shop_name' =>'门店',
					'market_name' =>'市场',
					'cj_timet' =>'成交时间',
					'business_type' =>'成交类型',
					'hulishi' =>'护理师',
				]; */

		$downUrl = exportExcelData('新单成交档案明细'.$endTime,$callName,$scarList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


	}

	//导出客户档案信息
	public function daochuclientinfo(){

		$start_time_int = 1640970061;
		$end_time_int = 1651338061;

		$where  = [];
		//array_push($where,['member_label','=',595]);
		// array_push($where,['member_classification','<>',0]);
		// array_push($where,['cj_time','=',0]);
		// array_push($where,['cj_money','=',0]);
		//array_push($where,['shop_id','=',79154893551569]);
		

		$customerList = Db::name('customer')->where($where)->column('id,shop_id,customer_name,telephone,member_classification');
		
		echo count($customerList);
		echo '<pre>';
		print_r($customerList);
		exit;

		// $where = [];

		// array_push($where,['id','in',$nurse_ids]);

		// $shop_personList = Db::name('shop_person')->where($where)->column('name','id');

		//echo count($customerList);exit;

		$where = [];

		array_push($where,['category_id','=',4]);

		$laiyuanList = Db::name('setting')->where($where)->column('name','id');

		$sexList = [0=>'未知',1=>'男',2=>'女'];

		// echo '<pre>';
		// print_r($customerList);
		foreach ($customerList as $key => $val){
			if($val['shop_id']!=0){


				$shopNameList = Db::name("shop")->where('shop_id', '=', $val['shop_id'])->column('shop_id','shop_name');

				$customerList[$key]['shopname'] = $shopNameList[0]['shop_name'];
			}else{
				$customerList[$key]['shopname'] = '';
			}

			//$customerList[$key]['nurse_name'] = $shop_personList[$val['nurse_id']] ?? '';



		}
		echo '<pre>';
		print_r($customerList);exit;
		$callName = [

					'customer_name'   =>'客户姓名',
					'telephone' =>'手机号',
					//'nurse_name' =>'护理师',
					'shop_name' =>'门店',
					//'laiyuan' =>'来源',
					'create_time' =>'录入时间',
					'first_time' =>'首次进店',
					'second_time' =>'二次进店',
					'consumption' =>'累计金额',
					'cj_money' =>'成交金额',
					'member_classification' =>'客户分类',
					//'remarks' =>'备注',
				];

		$downUrl = exportExcelData('客户档案',$callName,$customerList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";
	}

	//导出客户档案信息
	public function setservicecardadd(){

		if (empty($_FILES['file'])) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/setservicecardadd.html">返回【客户服务卡项新增】页面</a>';exit;

		}

		$tmp_name = $_FILES['file']['tmp_name'];

		if (empty($tmp_name) || !file_exists($tmp_name)) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/setservicecardadd.html">返回【客户服务卡项新增】页面</a>';exit;

		}


		$importData = readExcelData($tmp_name);

		$importData = array_values($importData);
		echo '<pre>';
		print_r($importData);
		$num = 0;

		foreach ($importData as $key => $val){

			/* $clientlist = Db::name("customer")

					->where('fcustId','=',$val[3])
					->where('king_no','=',$val[2])

					->column('id,shop_id,fcustId'); */

			$clientlist = Db::name("customer")

					->where('telephone','=',$val[1])
					->where('shop_id','=',73782774822370)

					->column('id,shop_id,telephone');

			echo '<pre>';
			print_r($clientlist);
			echo '</pre>';
			$addData = [];
			if(empty($clientlist)){
				echo '客户手机号+：'.$val[1].'客户内码+：'.$val[3];echo '<br/>';
			}else{

				$servicelist = Db::name("shop_goods")

					->where('name','=',$val[5])

					->column('id,category_id');


				if(empty($servicelist)){
					echo '服务卡项报错--'.$val[5];echo '<br/>';
				}else{

					$where = [];

					array_push($where,['name','=',$val[10]]);

					array_push($where,['shop_id','=',$clientlist[0]['shop_id']]);
					$shopPersonList = Db::name("shop_person")->where($where)->column('id,shop_name,name,shop_id');
					$one = [];

					$one['name'] = $val[5];

					$one['shop_id'] = $clientlist[0]['shop_id'];

					$one['customer_id'] = $clientlist[0]['id'];

					$one['service_type'] = $val[7] == '按次' ? 2: 1;

					if($one['service_type'] == 2){

						$one['num'] = $val[8];

						$one['start_time'] = 0;

						$one['end_time'] = 0;

					}else{

						$one['num'] = 0;

						$one['start_time'] =1640971159;//2022-01-01

						$one['end_time'] = intval(strtotime($val[9].' 00:00:00'));

					}

					$one['create_time'] = strtotime($val[0].' 00:00:00');

					$one['last_service_time'] = 0;

					$one['status'] = 1;

					$one['experience'] = 0;

					$one['kk_user_id'] = $shopPersonList[0]['id'];

					$one['kk_money'] = 0;

					$one['category_id'] = $servicelist[0]['category_id'];

					$one['goods_id'] = $servicelist[0]['id'];

					array_push($addData,$one);

				//$num ++;
				}

			}

			echo '<pre>';
			print_r($addData);
			//echo '</pre>';

			$res = Db::name("service")->insertAll($addData);
			echo '导入成功数据'.$res;
		}
	}

	//修改客户服务卡项的次数
	public function updateservicecard(){



		if (empty($_FILES['file'])) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/updateservicecard.html">返回【客户服务卡项修改】页面</a>';exit;

		}

		$tmp_name = $_FILES['file']['tmp_name'];

		if (empty($tmp_name) || !file_exists($tmp_name)) {

		    echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/updateservicecard.html">返回【客户服务卡项修改】页面</a>';exit;

		}


		$importData = readExcelData($tmp_name);

		$importData = array_values($importData);
		echo count($importData);
		echo '<pre>';
		print_r($importData);
		echo '</pre>';exit;

		foreach ($importData as $key => $val){

			$servicelog = Db::name("service_log")

				->where('service_id','=',$val[0])

				->column('count(id) as nid ,shop_id');
			$save=[];
			/* echo '<pre>';
			print_r($servicelog); */
			/*


			$save=[
				 //'num'=>$val[1],
				 'start_time'=>strtotime($val[1].' 10:20:35'),
				 'end_time'=>strtotime($val[2].' 00:00:00')+86399,
				]; */

			if($val[1]==0){
				$save=[
					 //'num'=>$val[1],
					 'status'=>2,
					 'gb_end_time'=>1654930143
					];
			}else{
				//$total =bcadd($val[1],$servicelog[0]['nid'],2);
				$save=[
					 'num'=>bcadd($val[1],$servicelog[0]['nid']),
					 //'id'=>$val[0],
					];


			}
			echo '<pre>';
			print_r($save);

			/* $res = Db::name('service')->where('id','=',$val[0])->update($save);

			echo '修改成功数据'.$res;echo '<br/>'; */
		}


	}


	//修改客户服务卡项的次数
	public function monthshujudaochu(){

		$where = [];

		//array_push($where,['type','=',1]);

		// array_push($where,['shop_id','=','73782774813542']);

		//array_push($where,['status','=',1]);

		//array_push($where,['shop_type','=',2]);

		array_push($where,['shop_id','=','73782774859952']);
		$customerInfos = Db::name('customer')->where($where)->column('id,customer_name,telephone,shop_id,fcustId,king_no');

		echo count($customerInfos);
		echo '<pre>';
		print_r($customerInfos);//exit;


		/* foreach ($customerInfos as $key => $val){

			$save=[
				 'fcustId'=>'',
				 'king_no'=>'',

				];

			$res = Db::name('customer')->where('id','=',$val['id'])->update($save);
		} */
	}




	//发送会员卡券的信息
	public function sendvipclient(){

		$where  = [];
		$xianshang = [584,118,55,54,52,51,50,43];
		$shopList = [73782775246614,73782775223895,73782775249465,73782775342427];
		array_push($where,['membership_leve','>',0]);
		//array_push($where,['shop_id','not in',$shopList]);
		array_push($where,['be_overdue_time','>=',1684166399]);
		$clientlist = Db::name('customer')->where($where)->column('id,shop_id,membership_leve,be_overdue_time,consumption');
		echo count($clientlist);
		/* echo '<pre>';
		print_r($clientlist);exit;
		 */
		$card_id = ['89'];

		$numb = 0;//白
		$numz = 0;//紫
		$numj = 0;//金
		//发送会员的会员日卡券
		foreach ($clientlist as $key => $val){

			$isSend = Db::name('coupon_provide_customer')->where('customer_id','=',$val['id'])->where('coupon_id','in',$card_id)->select()->toArray();

			if(empty($isSend)){


				$addData = [];

				$one = [];
				$one['coupon_id'] = 89;
				$one['customer_id'] = $val['id'];
				$one['staus'] = 0;
				$one['create_time'] = 1684141679;

				/* array_push($addData,$one);
				echo '<pre>';
				print_r($addData);

				$res = Db::name("coupon_provide_customer")->insertAll($addData);
				echo '导入成功数据'.$res; */

			}

		}
		exit;
		/* echo '白玉兰会员数据---'.$numb;echo '<br/>';
		echo '紫玉兰会员数据---'.$numz;echo '<br/>';
		echo '金玉兰会员数据---'.$numj;echo '<br/>';exit; */


	}

	public function setclientlistu(){

		echo "修改余额表门店归属";echo "<br/>";

		$shop_id = Db::name('shop')->where('shop_name','=','西安门诊')->value('shop_id');

		echo "门店id".$shop_id;echo "<br/>";

		// 余额表顾客的门店
		/* $list = Db::name('customer_balance')
				->where('shop_id','=',$shop_id)
				->column('shop_id','customer_id'); */

		$list = Db::name('customer')
				->where('shop_id','=',$shop_id)
				->column('shop_id','id');
		echo "总共顾客数：".count($list);echo "<br/>";
		/* echo "<pre>";
		print_r($list);
		exit; */


		$update_ids = [];
		$updateNum = 0;
		$num = 0;
		$delnum = 0;
		foreach ($list as $key => $value) {
			$customer_shop_id = Db::name('customer_balance')->where('customer_id','=',$key)->column('id,shop_id');

			if(empty($customer_shop_id)){
				$delete_where = [];
				$delnum++;
				array_push($delete_where,['customer_id','=',$key]);

				Db::name('customer_balance')->where($delete_where)->delete();

				array_push($delete_where,['time','in',[20220530,20220531,20220601,20220602,20220603]]);

				Db::name('customer_balance_tj')->where($delete_where)->delete();

			}else{
				if($customer_shop_id[0]['shop_id'] == $value){
					$num++;
				}else{
					$updateNum++;
					array_push($update_ids,$key);
					$save=[
						'shop_id'=>$value,
					];

					$bl_where = [];
					array_push($bl_where,['customer_id','=',$key]);

					Db::name('customer_balance')->where($bl_where)->update($save);

					$tj_where = [];

					array_push($tj_where,['customer_id','=',$key]);
					array_push($tj_where,['time','in',[20220530,20220531,20220601,20220602,20220603]]);

					Db::name('customer_balance_tj')->where($tj_where)->update($save);
				}
			}


		}

		echo "<pre>";
		print_r($update_ids);
		echo '需要删除的'.$delnum;echo '<br/>';
		echo "不用修改".$num;echo "<br/>";
		echo "修改".$updateNum;echo "<br/>";


	}







	public function qkcustomeryucun(){

		echo "清空门店客户预存";echo "<br/>";

		$shop_id = Db::name('shop')->where('shop_name','=','西安门诊')->value('shop_id');

		echo "门店id".$shop_id;echo "<br/>";

		// 余额表顾客的门店
		$list = Db::name('customer_balance')
				->where('shop_id','=',$shop_id)
				->column('id,shop_id,customer_id,money,complimentary_money');

		echo "总共顾客预存数：".count($list);echo "<br/>";
		/* echo "<pre>";
		print_r($list);
		exit; */

		foreach ($list as $key => $value) {
			$save=[
				'money'=>0.00,
				'complimentary_money'=>0.00,
			];
			$bl_where = [];
			array_push($bl_where,['id','=',$value['id']]);

			Db::name('customer_balance')->where($bl_where)->update($save);

		}


	}



	public function sendeccomeinfo($customerId){

		//echo '';
		//通过 客户ID  查询客户信息
		//$customerId = '377266';
		$customerInfo = Db::name('customer')->where('id','=',$customerId)->column('id,customer_name,source_id,telephone,shop_id,effective_entry,operator,create_time,first_time,second_time');

		//EC系统中客服的 id   2022.06.01
		$kefu_EC['521']=17587172; //凡大雪
		$kefu_EC['521']=17587180; //卢越
		$kefu_EC['520']=17587184; //曹艳春
		$kefu_EC['525']=17587188; //刘芳芳
		$kefu_EC['523']=17587202; //付一鸣
		$kefu_EC['527']=17587206; //魏李雪
		$kefu_EC['948']=17587226; //王雯雯
		$kefu_EC['947']=17587236; //万川源
		$kefu_EC['524']=17587240; //杨玉秀
		$kefu_EC['524']=17957856; //凡冬冬

		$ec_kefuId = $kefu_EC[$customerInfo[0]['operator']];//EC 中的客服ID
		$ec_phone = $customerInfo[0]['telephone']; //EC 中的客户手机号

		//获取自定义字段的信息---接口
		$filedParam = $this->checkfiledparam();
		$newFiledParam = json_decode($filedParam['result'],true);

		// 通过手机号调取 EC接口获取 客户在EC中的 客户ID
		$clientParam = $this->lookcustomerId($ec_phone);
		$clientListParam = json_decode($clientParam['result'],true);
		$crmId = $clientListParam['data'][0]['list'][0]['crmId'];

		$updatejdParam = $this->updateSetjindian($ec_kefuId,$crmId);
		$updatejdLiParam = json_decode($updatejdParam['result'],true);


		$filedParamT = $this->setcustomerjs($crmId);
		$newFiledParamT = json_decode($filedParamT['result'],true);


	}

	/**
	 *  签名算法
	 *  该方法可以公用
	 * @param int $timeStamp
	 * @param string $appId
	 * @param string $appSecret
	 * @return string 返回签名数据
	 */
	function getSign($timeStamp, $appId, $appSecret)
	{
	    $sign = "appId={$appId}&appSecret={$appSecret}&timeStamp={$timeStamp}";
	    return strtoupper(md5($sign));
	}

	/**
	 * post 业务请求
	 * 该方法可以公用
	 * @param string $url
	 * @param string $jsonData
	 * @param int $cid
	 * @param string $appId
	 * @param string $appSecret
	 */
	function http_post_json($url, $jsonData, $cid, $appId, $appSecret)
	{
	    // 1. 获取当前时间戳
	    $timeStamp = time() * 1000;
	    // 2. 获取签名
	    $sign =$this-> getSign($timeStamp, $appId, $appSecret);
	    // 3. 封装请求头
	    $head = array(
	        'Content-Type: application/json; charset=utf-8',
	        'X-Ec-Cid: ' . $cid,
	        'X-Ec-Sign: ' . $sign,
	        'X-Ec-TimeStamp: ' . $timeStamp,
	        'Content-Length: ' . strlen($jsonData)
	    );

	    // 3. 传入http 参数
	    $ch = curl_init();
	    curl_setopt($ch, CURLOPT_POST, 1);
	    curl_setopt($ch, CURLOPT_URL, $url);
	    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	    // https 支持 - 对认证证书来源的检查
	    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	    // 请求数据放这里
	    curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonData);
	    // head
	    curl_setopt($ch, CURLOPT_HTTPHEADER, $head);

	    // 请求服务器
	    $response = curl_exec($ch);
	    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
	    curl_close($ch);

	    // 组织，返回结果和响应码
	    return array(
	        $httpCode,
	        $response
	    );
	}

	function doPost($url, $jsonData)
	{
	    $cid = 16317459; // 需要换成您自己的 企业id
	    $appId = "770275123021217792"; // 需要换成您自己的 appId
	    $appSecret = "elzRSAEm75iypsL1SGy"; // 需要换成您自己的 appSecret
	    return $this->http_post_json($url, $jsonData, $cid, $appId, $appSecret);
	}
	//通过手机号查询 信息
	function setlabelinfo(){
		// 1. 组装请求数据
		$data = array(
		   "groupValue"=>''
		);
		$jsonData = json_encode($data);

		// 2. 请求服务器处理
		$url = "https://open.workec.com/v2/customer/queryExist";
		list ($returnCode, $returnContent) =$this->doPost($url, $jsonData);

		// 3. 输出结果：处理您的结果
		$info['code'] = $returnCode;
		$info['result']  = $returnContent;
		return $info;
	}
	function doGet($url)
	{
	    $cid = 16317459; // 需要换成您自己的 企业id
	    $appId = "770275123021217792"; // 需要换成您自己的 appId
	    $appSecret = "elzRSAEm75iypsL1SGy"; // 需要换成您自己的 appSecret
	    return $this->http_get($url, $cid, $appId, $appSecret);
	}
	/**
	 * get 请求业务
	 * 该方法可以公用   ---get方法
	 *
	 * @param string $url
	 * @param string $jsonData
	 * @param int $cid
	 * @param string $appId
	 * @param string $appSecret
	 */
	function http_get($url, $cid, $appId, $appSecret)
	{
	    // 1. 获取当前时间戳
	    $timeStamp = time() * 1000;
	    // 2. 获取签名
	    $sign =$this-> getSign($timeStamp, $appId, $appSecret);
	    // 3. 封装请求头
	    $head = array(
	        'Content-Type: application/json; charset=utf-8',
	        'X-Ec-Cid: ' . $cid,
	        'X-Ec-Sign: ' . $sign,
	        'X-Ec-TimeStamp: ' . $timeStamp
	    );
	    // 3. 传入http 参数
	    $ch = curl_init();
	    curl_setopt($ch, CURLOPT_URL, $url);
	    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	    // https 支持 - 对认证证书来源的检查
	    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	    // head
	    curl_setopt($ch, CURLOPT_HTTPHEADER, $head);

	    // 请求服务器
	    $response = curl_exec($ch);
	    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
	    curl_close($ch);
	    // 组织，返回结果和响应码
	    return array(
	        $httpCode,
	        $response
	    );
	}

	function checkfiledparam()
	{

	    // 2. 请求服务器处理
	    $url = "https://open.workec.com/v2/config/getFieldMapping";
	    list ($returnCode, $returnContent) =$this-> doGet($url);

		$info['code'] = $returnCode;
		$info['result']  = $returnContent;

		return $info;

	}

	//通过 客户手机号 查询客户的基础  crmid---05-31
	function lookcustomerId($tellphone)
	{
	    // 1. 组装请求数据
	    $data = array(
	       "mobile"=>$tellphone
	    );
	    $jsonData = json_encode($data);

	    // 2. 请求服务器处理
	    $url = "https://open.workec.com/v2/customer/queryExist";
	    list ($returnCode, $returnContent) =$this->doPost($url, $jsonData);

	    // 3. 输出结果：处理您的结果
	    $info['code'] = $returnCode;
	    $info['result']  = $returnContent;

		return $info;
	}

	//通过 客户手机号 查询客户的基础  crmid---05-31
	function updateSetjindian($ec_kefuId,$crmId)
	{
	    // 1. 组装请求数据
	    $data = array(
	       "optUserId"=>$ec_kefuId,
		   "crmId"=>$crmId,
		    "fields"=>[
			  "81995985"=>"2630724"

		   ]
	    );

	    $jsonData = json_encode($data);

	    // 2. 请求服务器处理
	    $url = "https://open.workec.com/v2/customer/updateCustomer";
	    list ($returnCode, $returnContent) =$this->doPost($url, $jsonData);

	    // 3. 输出结果：处理您的结果
	    $info['code'] = $returnCode;
	    $info['result']  = $returnContent;

		return $info;
	}

	//通过 post 查询客户的基础信息档案---05-31
	function setcustomerjs($crmId)
	{
	    // 1. 组装请求数据
	    $data = array(
	       "crmIds"=>$crmId
	    );
	    $jsonData = json_encode($data);

	    // 2. 请求服务器处理
	    $url = "https://open.workec.com/v2/customer/preciseQuery";
	    list ($returnCode, $returnContent) =$this->doPost($url, $jsonData);

	    // 3. 输出结果：处理您的结果
	    $info['code'] = $returnCode;
	    $info['result']  = $returnContent;

		return $info;
	}




	function setceshiclient(){

		$start_time_int = '1656608461';

		$end_time_int = '1658336461';

		$shopIds = '73782774881835';
		$xianshang = [44];

		$where = [];

		array_push($where,['or.deal_time','>=',$start_time_int]);   //成交时间

		array_push($where,['or.deal_time','<=',$end_time_int]);

		array_push($where,['or.king_status','=',1]);    // 推送状态 1是成功

		array_push($where,['or.type','=',3]);   //收款单

		array_push($where,['or.shop_id','in',$shopIds]);

		array_push($where,['cs.source_id','in',$xianshang]);

		$zcjAllList = Db::name('shop_refund_order')->alias('or')

		                ->join('customer cs', 'or.uid=cs.id', 'LEFT')

		                ->where($where)

		                //->group('shop_id')

		                //->column('sum(pay_amount) as money','or.shop_id');
						->column('cs.create_time,cs.customer_name,cs.telephone,cs.first_time,cs.second_time,cs.source_id,or.deal_time,or.total_amount,or.pay_amount,or.order_type');

		foreach($zcjAllList as $key => $val)
		{

			$zcjAllList[$key]['create_timeT'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';
			$zcjAllList[$key]['first_timeT'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';
			$zcjAllList[$key]['second_timeT'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';
			$zcjAllList[$key]['deal_timeT'] = $val['deal_time'] > 0 ? date('Y-m-d H:i:s',$val['deal_time']) : '';

		}
		/* echo '<pre>';
		print_r($zcjAllList); */



		$callName = [

		    'create_timeT'   =>'录入日期',

		    'customer_name'   =>'姓名',

			'telephone'   =>'手机号',

			'source_id'   =>'来源',

			'first_timeT'   =>'首次进店',

			'second_timeT'   =>'二次进店',

			'deal_timeT'   =>'成交日期',

			'pay_amount'   =>'成交本金',

			'order_type'   =>'新/补单',
		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('百度医疗'.$timeStr,$callName,$zcjAllList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}

	public function setcustomercjnum()

	{

		$start_time_int = mktime(0,0,0,9,1,2022);
		$end_time_int = mktime(23,59,59,9,30,2022);
		//exit;
		$where  = [];

		array_push($where,['type','=',1]);

		array_push($where,['shop_id','<>',74076159037951]);

		array_push($where,['shop_type','=',2]);//
		array_push($where,['status','=',1]);//

		$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');

		/* echo '<pre>';
		print_r($shopList);exit; */

		$xianshang = [44,50,51,52,54,55,118,584,609];
		$num = 0;
		foreach($shopList as $key => $val)

		{
			// 获取疤痕进店总数

			$type = [1,2,3,4,5,6,7,8];  //获取疤痕进店总数
			//$type = [9,11,12];  //获取项目进店总数
			$where = [];

			array_push($where,['c.first_time','>=',$start_time_int]);

			array_push($where,['c.first_time','<=',$end_time_int]);

			$whereOr = [];

			array_push($whereOr,['c.second_time','>=',$start_time_int]);

			array_push($whereOr,['c.second_time','<=',$end_time_int]);

			$bdjdAllList1 = Db::name('customer')->alias('c')

			            ->join('customer_scar cg', 'cg.customer_id=c.id', 'LEFT')

			            ->where([['c.shop_id','=',$val['shop_id']]])

			            ->where([['c.source_id','in',$xianshang]])

			            ->where([['cg.customer_setting_id_type','in',$type]])

			            ->where(function($query) use ($where,$whereOr) {

			                    $query -> where(function($query) use ($where) {

			                        $query -> where($where);

			                    });

			                    $query -> whereOr(function($query) use ($whereOr) {

			                        $query -> where($whereOr);

			                    });

			                })


			            ->group('shop_id')

			            ->column('count(distinct c.id) as num');
			if(!empty($bdjdAllList1)){
				$shopList[$key]['bhNum'] = $bdjdAllList1[0];
			}else{
				$shopList[$key]['bhNum'] = 0;
			}

			//成交疤痕人数  business_type 1是疤痕 2是项目
			$scarList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time_int)//2022.01.01
			    ->where('b.cj_time','<=',$end_time_int) //2022.01.31
			    ->where('b.cj_money','>',0)
				->where('b.source_id','in',$xianshang)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',1)
			    ->where('sp.type','=',3)
				->where('sp.shop_id','=',$val['shop_id'])
			    ->column('count(b.id) as count');

			//成交疤痕人数  business_type 1是疤痕 2是项目
			$xiangmuList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time_int)//2022.01.01
			    ->where('b.cj_time','<=',$end_time_int) //2022.01.31
			    ->where('b.cj_money','>',0)
				->where('b.source_id','in',$xianshang)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',2)
			    ->where('sp.type','=',3)
				->where('sp.shop_id','=',$val['shop_id'])
			    ->column('count(b.id) as count');


			$shopList[$key]['bhCjNum'] = $scarList[0];
			$shopList[$key]['xmCjNum'] = $xiangmuList[0];


			//成交疤痕金额  business_type 1是疤痕 2是项目
			$scarListAmount = Db::name('customer')
			    ->alias('b')
			    //->where('b.cj_time','>=',$start_time_int)//2022.01.01
			    //->where('b.cj_time','<=',$end_time_int) //2022.01.31
			    //->where('b.cj_money','>',0)
				->where('b.source_id','in',$xianshang)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',1)
			    ->where('sp.type','=',3)
				->where('sp.status','=',2)
				->where('sp.refund_type','<>','101.03')
				->where('sp.shop_id','=',$val['shop_id'])
			    ->column('count(sp.pay_amount) as count');

			echo '<pre>';
			print_r($scarListAmount);

		}
		// 获取项目进店总数
		foreach($shopList as $key => $val)

		{

			$type = [9,11,12];

			$where = [];

			array_push($where,['c.first_time','>=',$start_time_int]);

			array_push($where,['c.first_time','<=',$end_time_int]);

			// array_push($where,['cg.customer_setting_id_type','in',$type]);

			// array_push($where,['c.shop_id','in',$shopIds]);

			$whereOr = [];

			array_push($whereOr,['c.second_time','>=',$start_time_int]);

			array_push($whereOr,['c.second_time','<=',$end_time_int]);

			// array_push($whereOr,['cg.customer_setting_id_type','in',$type]);

			// array_push($whereOr,['c.shop_id','in',$shopIds]);

			$xmjdAllList1 = Db::name('customer')->alias('c')

			            ->join('customer_scar cg', 'cg.customer_id=c.id', 'LEFT')



			            ->where([['c.shop_id','in',$val['shop_id']]])

			            ->where([['c.source_id','in',$xianshang]])

			            ->where([['cg.customer_setting_id_type','in',$type]])

			            ->where(function($query) use ($where,$whereOr) {

			                    $query -> where(function($query) use ($where) {

			                        $query -> where($where);

			                    });

			                    $query -> whereOr(function($query) use ($whereOr) {

			                        $query -> where($whereOr);

			                    });

			                })

			            ->group('shop_id')

			            // ->column('count(c.id)','shop_id');

			            ->column('count(distinct c.id) as num');


			if(!empty($xmjdAllList1)){
				$shopList[$key]['xmNum'] = $xmjdAllList1[0];
			}else{
				$shopList[$key]['xmNum'] = 0;
			}
		}
		/* echo '<pre>';
		print_r($shopList);
		exit; */

		$bhNum = 0;
		$bhCJNum = 0;
		$xmCJNum = 0;
		$xmNum = 0;
		foreach($shopList as $key => $val)
		{

			$bhNum+=$val['bhNum'];
			$xmNum+=$val['xmNum'];

			$bhCJNum+=$val['bhCjNum'];
			$xmCJNum+=$val['xmCjNum'];

		}
		echo '疤痕进店总数--'.$bhNum;echo '<br/>';
		echo '项目进店总数--'.$xmNum;echo '<br/>';

		echo '疤痕成交总数--'.$bhCJNum;echo '<br/>';
		echo '项目成交总数--'.$xmCJNum;echo '<br/>';
	}

	public function setbandxmamount(){

		$start_time_int = mktime(0,0,0,2,1,2023);
		$end_time_int = mktime(23,59,59,2,28,2023);
		//exit;
		$where  = [];

		array_push($where,['type','=',1]);

		array_push($where,['shop_id','<>',74076159037951]);

		array_push($where,['shop_type','=',2]);//
		array_push($where,['status','=',1]);//

		//$shopList = Db::name('shop')->where($where)->column('shop_id,shop_name,market_name');
		$shopList = Db::name('shop')->where($where)->field('shop_id,shop_name,market_id,market_name,shop_type')->select()->toArray();

		$shopIds = array_values(array_unique(array_column($shopList,'shop_id')));
		/* echo '<pre>';
		print_r($shopIds);exit; */

		$xianshang = [44,50,51,52,54,55,118,584,609];

		$scarListAmount =Db::name('shop_refund_order')
			->where('deal_time','>=',$start_time_int)
			->where('deal_time','<=',$end_time_int)
			->where('shop_id','in',$shopIds)
			->where('type','=',3)
			->where('status','=',2)
			->where('business_type','=',1)
			->where('order_type','=',1)
			->where('refund_type','<>',101.03)
			->column('pay_amount,shop_id,uid,business_type');

		$xiangmuListAmount =Db::name('shop_refund_order')
			->where('deal_time','>=',$start_time_int)
			->where('deal_time','<=',$end_time_int)
			->where('shop_id','in',$shopIds)
			->where('type','=',3)
			->where('status','=',2)
			->where('business_type','=',2)
			->where('order_type','=',1)
			->where('refund_type','<>',101.03)
			->column('pay_amount,shop_id,uid,business_type');


		$bhAmount = 0;
		//$bhAmount = 0;
		foreach($scarListAmount as $key => $val)
		{
			//成交人数--tj
			$kehuId =Db::name('customer')

				->where('id','=',$val['uid'])
				->where('source_id','in',$xianshang)
				->column('id,customer_name,telephone,source_id,shop_id');

			if(!empty($kehuId)){
				$bhAmount+= $val['pay_amount'];
			}
		}
		$xmAmount = 0;
		foreach($xiangmuListAmount as $key => $val)
		{
			//成交人数--tj
			$kehuId =Db::name('customer')

				->where('id','=',$val['uid'])
				->where('source_id','in',$xianshang)
				->column('id,customer_name,telephone,source_id,shop_id');

			if(!empty($kehuId)){
				$xmAmount+= $val['pay_amount'];
			}
		}
		echo '疤痕成交金额--'.$bhAmount;echo '<br/>';
		echo '项目成交金额--'.$xmAmount;echo '<br/>';
	}


	public function setDshiqi(){

		$time = date('Ymd');
		$res = Db::name('customer_balance_tj')->where('time','=',$time)->find();
		/* if($res){
		    return resultArray(['data'=>[],'error'=>'已被处理']);
		} */
		$where = [];
		array_push($where,['type','=',1]);
		array_push($where,['shop_id','<>',73782774792133]);

		//$zyShopIds = Db::name('shop')->where('type','=',1)->column('shop_id');
		$zyShopIds = Db::name('shop')->where($where)->column('shop_id,shop_name');

		//$zyShopIds = [,73782775492343,73782775382480,73782775368291,73782775339453,73782775382138,73782775394959,73782775377728];
		/*   */
		echo '<pre>';
		print_r($zyShopIds);
		exit;
		$zyB = Db::name('customer_balance')->where('shop_id','in',$zyShopIds)->column('customer_id,balance_category_id,money,complimentary_money,shop_id');
		$b = ['time'=>$time];
		array_walk($zyB, function(&$val, $key) use ($b) {
		    $val = array_merge($val, $b);
		});

		/* $res = Db::name('customer_balance_tj')->insertAll($zyB);

		return resultArray(['data'=>[],'error'=>'处理成功']); */
		/* echo count($zyB);
		echo '<pre>';
		print_r($zyB);exit; */
	}

	public function setDongbeiCustomer(){

		echo '东北市场-客户分析';

		$start_time = strtotime('2024-04-01 00:00:00');
		$end_time = strtotime('2024-04-30 23:59:59');

		$where = [];


		//$shop_list = [73782775246614,73782775013838,73782775155221,73782775102252,73782775096910,73782775133028];

		/* $qingchunddou = [10];
		$bahen = [1,2,3,4,5,6,7,8];
		$pifumeirong = [9,11,12]; */

		array_push($where,['create_time','>=',$start_time]);
		array_push($where,['create_time','<=',$end_time]);
		//array_push($where,['shop_id','in',$shop_list]);

		array_push($where,['source_id','=',118]);

		$customer_list = Db::name('customer')->where($where)
		// ->count();
		// ->limit(10000,5000)
		->column('id,customer_name,sex,telephone');

		echo count($customer_list);echo '<br/>';
		$bahCount = 0;
		$bahCmCount = 0;
		$bahMoneyCount = 0;

		$doudCount = 0;
		$doudCmCount = 0;
		$ddMoneyCount = 0;

		$pfCount = 0;
		$pfCmCount = 0;
		$pfMoneyCount = 0;

		foreach($customer_list as $key => $val){
			//echo $val['id'];echo '<br/>';

			$res = Db::name('customer_scar')->where('customer_id','=',$val['id'])->column('customer_setting_id_type,customer_id');

			/* echo '<pre>';
			print_r($res); */

			if(empty($res)){

			}else{
				//青春痘
				if($res[0]['customer_setting_id_type']==10){
					$doudCount++;
					$doudlist = Db::name('customer')->where('id','=',$res[0]['customer_id'])->column('id,first_time,cj_time,telephone,consumption');
					if($doudlist[0]['first_time']>0){
						$doudCmCount+=count($doudlist);

						$ddMoneyCount+=$doudlist[0]['consumption'];
					}

				}
				//疤痕
				if($res[0]['customer_setting_id_type']<=8){
					$bahCount++;

					$bahlist = Db::name('customer')->where('id','=',$res[0]['customer_id'])->column('id,first_time,cj_time,telephone,consumption');
					if($bahlist[0]['first_time']>0){
						$bahCmCount+=count($bahlist);

						$bahMoneyCount+=$bahlist[0]['consumption'];
					}
				}

				//皮肤
				if($res[0]['customer_setting_id_type']>10 || $res[0]['customer_setting_id_type'] ==9){//
					$pfCount++;

					$pifulist = Db::name('customer')->where('id','=',$res[0]['customer_id'])->column('id,first_time,cj_time,telephone,consumption');
					if($pifulist[0]['first_time']>0){
						$pfCmCount+=count($pifulist);

						$pfMoneyCount+=$pifulist[0]['consumption'];
					}
				}
			}

		}

		echo '青春痘数量---'.$doudCount;echo '<br/>';
		echo '青春痘进店-数量---'.$doudCmCount;echo '<br/>';
		echo '青春痘实收-金额---'.$ddMoneyCount;echo '<br/>';

		echo '疤痕数量---'.$bahCount;echo '<br/>';
		echo '疤痕进店数量---'.$bahCmCount;echo '<br/>';
		echo '疤痕实收金额---'.$bahMoneyCount;echo '<br/>';


		echo '皮肤数量---'.$pfCount;echo '<br/>';
		echo '皮肤进店数量---'.$pfCmCount;echo '<br/>';
		echo '皮肤实收金额---'.$pfMoneyCount;echo '<br/>';exit;
		exit;
		exit;

	}


	public function setproductlist(){

		$start_time = mktime(0,0,0,9,1,2022);
		$end_time = mktime(23,59,59,12,31,2022);


		/* $where = [];
		array_push($where,['deal_time','>=',$start_time]);
		array_push($where,['deal_time','<=',$end_time]);
		//array_push($where,['shop_id','in',$shopIds]);
		array_push($where,['status','=',2]);
		 */

		$shopIds = [73782774813357];

		$where = [];

		array_push($where,['or.deal_time','>=',$start_time]);   //成交时间

		array_push($where,['or.deal_time','<=',$end_time]);

		array_push($where,['or.status','=',2]);    // 推送状态 1是成功

		array_push($where,['or.shop_id','in',$shopIds]);


		$goodList = Db::name('shop_order')->alias('or')

		                ->join('shop_order_goods g', 'or.id=g.order_id', 'LEFT')

		                ->where($where)

		                //->group('shop_id')

		                ->column('g.order_id,or.uid,g.number,g.pay_price,g.goods_price,g.goods_id,or.deal_time,or.shop_id,g.goods_name,g.create_time');


		echo count($goodList);
		/* echo '<pre>';
		print_r($goodList); exit; */

		$productList = Db::name('shop_goods')->column('goods_type','id');

		$kt = 0;
		foreach ($goodList as $k => $v){

			$productGoodsList[$kt]['uid'] = $v['uid'];
			$productGoodsList[$kt]['order_id'] = $v['order_id'];
			$productGoodsList[$kt]['shop_id'] = $v['shop_id'];
			$productGoodsList[$kt]['name'] = $v['goods_name'];
			$productGoodsList[$kt]['goods_id'] = $v['goods_id'];
			$productGoodsList[$kt]['category_id'] = $productList[$v['goods_id']];
			$productGoodsList[$kt]['goods_price'] = $v['goods_price'];
			$productGoodsList[$kt]['pay_price'] = $v['pay_price'];
			$productGoodsList[$kt]['number'] = $v['number'];
			$productGoodsList[$kt]['deal_time'] = $v['deal_time'];
			$productGoodsList[$kt]['create_time'] = $v['create_time'];
			$productGoodsList[$kt]['status'] = 1;


			$kt++;
		}
		Db::name('shop_goods_analyse')->insertAll($productGoodsList);
	}
	
	/* 更新营销中心 客服录入档案明细  */
	public function updateCustomerList(){

		$where = [];

		array_push($where,['deal_time','>=',1676044800]); //成交时间 大于等于 2023-03-01
		array_push($where,['pay_amount','>=','2000']); //单笔订单成交金额 大于等于2000
		array_push($where,['type','=','3']); // 收款单
		array_push($where,['refund_type','=','101.02']); // 收款单 支选择 收款类型的
		array_push($where,['status','=','2']); // 收款单
		$customer_list = Db::name('shop_refund_order')->order('shop_id')->where($where)->column('uid,consignee,sale_name,shop_name,pay_amount,deal_time');




		foreach ($customer_list as $k => $v){

			$customer_list[$k]['dealTimeT'] = $v['deal_time'] > 0 ? date('Y-m-d',$v['deal_time']) : '';
		}

		/* echo '<pre>';
		print_r($customer_list); */

		$callName = [

		    'dealTimeT'   =>'成交日期',

		    'uid'   =>'客户ID',

			'consignee'   =>'客户姓名',

			'shop_name'   =>'门店',

			'sale_name'   =>'护理师',

			'pay_amount'   =>'成交金额',
		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('3月活动护理师数据'.$timeStr,$callName,$customer_list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";




		exit;


		/* $customer_list = Db::name('customer')->where($where)

		->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,creator_id,operator,nurse_id,remarks,first_time,second_time,effective_entry,cj_time,consumption,member_label,cj_money,membership_leve,be_overdue_time,introducer');

		echo "<pre>";
		print_r($customer_list);
		echo "</pre>";
		exit; */
		$personShops = Db::name('shop_person')->column('id','name');
		$personIds = Db::name('admin_user')->column('realname','id');
		/* echo '<pre>';
		print_r($personIds);exit; */
		$where = [];

		array_push($where,['create_time','>=',1675184461]);   //成交时间
		array_push($where,['type','=',0]);   //成交时间
		$customer_list = Db::name('admin_login_record')->where($where)

		->column('id,create_user_id,create_time');

		foreach ($customer_list as $k => $v){
			$personList = Db::name('shop_person')

				->where('id','=',$v['create_user_id'])
				->column('id,shop_id,shop_name,name');

			/* echo '<pre>';
			print_r($personList); */

			if(empty($personList)){

				$customer_list[$k]['loginName'] = $personIds[$v['create_user_id']];
				$customer_list[$k]['shopName'] = '666';
			}else{
				$customer_list[$k]['loginName'] = $personList[0]['name'];
				$customer_list[$k]['shopName'] = $personList[0]['shop_name'];
			}




			$customer_list[$k]['login_time'] = date('Y-m-d',$v['create_time']);

		}

			/* echo '<pre>';
			print_r($customer_list); */
		//echo count($list);


		$callName = [

		    'login_time'   =>'登录日期',

		    'loginName'   =>'姓名',

			'shopName'   =>'门店',


		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('2月门店登录中台频次',$callName,$customer_list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}


	/* 3月活动数据导出 --单笔订单成交金额数据大于等于2000的护理师成交个数 */
	public function setThreeNuriceShopAmount(){

		$where = [];

		array_push($where,['deal_time','>=',1677600000]); //成交时间 大于等于 2023-03-01
		array_push($where,['pay_amount','>=','2000']); //单笔订单成交金额 大于等于2000
		array_push($where,['type','=','3']); // 收款单
		array_push($where,['refund_type','=','101.02']); // 收款单 支选择 收款类型的
		array_push($where,['status','=','2']); // 收款单
		$customer_list = Db::name('shop_refund_order')->order('shop_id')->where($where)->column('uid,consignee,sale_name,shop_name,pay_amount,deal_time');




		foreach ($customer_list as $k => $v){

			$customer_list[$k]['dealTimeT'] = $v['deal_time'] > 0 ? date('Y-m-d',$v['deal_time']) : '';
		}

		/* echo '<pre>';
		print_r($customer_list);exit; */

		$callName = [

		    'dealTimeT'   =>'成交日期',

		    'uid'   =>'客户ID',

			'consignee'   =>'客户姓名',

			'shop_name'   =>'门店',

			'sale_name'   =>'护理师',

			'pay_amount'   =>'成交金额',
		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('3月活动护理师数据'.$timeStr,$callName,$customer_list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";




		exit;
	}

	/* 每月2号的数据输出报表 */

	public function setMonthShujuDaochuList(){

		$date = '2023-05-05';

		$start_time = mktime(0,0,0,4,1,2023);
		$end_time = mktime(23,59,59,4,30,2023);

		$where = [];

		$shopT = [74076159037951,73782774745269,73782774792133];

		array_push($where,['type','=',1]);
		array_push($where,['shop_type','=',2]);
		array_push($where,['shop_id','not in',$shopT]);

		//$zyShopIds = Db::name('shop')->where('type','=',1)->column('shop_id');
		$shopList = Db::name('shop')->where($where)->column('shop_id,market_id,shop_name');



		foreach ($shopList as $k => $v){

			//查询优秀案例
			$wherec = [];
			array_push($wherec,['sp_excellent_time','>=',$start_time]);
			array_push($wherec,['sp_excellent_time','<=',$end_time]);
			array_push($wherec,['shop_id','=',$v['shop_id']]);
			array_push($wherec,['selection_results','in',[2,3]]);
			$shopCaseList = Db::name('case')->where($wherec)->column('count(id),shop_id');
			$shopList[$k]['caseNum'] = $shopCaseList[0]['count(id)'];


			//门店疤痕补单 和项目补单业绩

			if($v['market_id'] == 8 || $v['market_id'] == 9 || $v['market_id'] == 10|| $v['market_id'] == 11)
			{
				$newsBDMoney = $this->sumShopPayMoneyNewMZ('XM',$v['shop_id'],$start_time,$end_time);
				$budanMoney = $this->sumShopPayMoneyNewMZ('BH',$v['shop_id'],$start_time,$end_time);
			}else{
				$newsBDMoney = $this->sumShopPayMoneyNew('XM',$v['shop_id'],$start_time,$end_time);
				$budanMoney = $this->sumShopPayMoneyNew('BH',$v['shop_id'],$start_time,$end_time);
			}

			$shopList[$k]['xmAmount'] = $newsBDMoney;
			$shopList[$k]['bhAmount'] = $budanMoney;



			//查询到店率


			$cj_customer_ids = $this->cj_customer($v['shop_id']);
			/* echo '<pre>';
			print_r($cj_customer_ids); */

			$baoyouNum = $this->baoyou_customer($cj_customer_ids,$date);

			$byServiceNum = $this->setByServiceList($baoyouNum,$start_time,$end_time);

			/* $shopList[$k]['byNum_s'] = count($byServiceNum);
			$shopList[$k]['byNum_cj'] = count($baoyouNum); */

			$shopList[$k]['byNum'] =count($baoyouNum) ? (bcdiv(count($byServiceNum),count($baoyouNum),2)*100).'%' : '空';




		}
		/* echo '<pre>';
		print_r($shopList);

		exit; */
		$callName = [

		    'shop_name'   =>'门店',

		    'caseNum'   =>'优案数量',

			'xmAmount'   =>'项目补单',

			'bhAmount'   =>'疤痕补单',

			/* 'byNum_s'   =>'到店服务',

			'byNum_cj'   =>'保有客户', */

			'byNum'   =>'到店率',
		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('客户盘点表数据'.$timeStr,$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";
		exit;

	}

	/* 查询保有率是否 有在规定时间 进行 到店核销 */

	public function setByServiceList($byIds,$start_time,$end_time){

		$where = [];

		array_push($where,['id','in',$byIds]);
		array_push($where,['final_consumption','>=',$start_time]);
		array_push($where,['final_consumption','<=',$end_time]);

		$customer_service = Db::name('customer')->where($where)->column('id');

		return $customer_service;

	}

	//查询补单是疤痕 还是项目的 函数
	public function sumShopPayMoneyNew($scene,$shopId,$startTime,$endTime){

		switch ($scene) {
			case 'XM':

		        $where['order_type']=2;
				$wheret['r.order_type']=2;

				$where['business_type']=2;
				$wheret['r.business_type']=2;

				break;

		    case 'BH':

		        $where['order_type']=2;
				$wheret['r.order_type']=2;

				$where['business_type']=1;
				$wheret['r.business_type']=1;
				break;

		    default:

		        break;

		}
		//收款单中的疤痕业绩 不含店转的业绩--01
		$receive = Db::name('shop_refund_order')->alias('r')

	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

	        //->field('sum(g.pay_price * r.coefficient) as pay_amount')
			->field('sum(g.pay_price) as pay_amount')
	        ->where('r.shop_id','=',$shopId)

	        ->where($wheret)

	        ->where('g.refund_mode','<>','06')

	        ->where('r.type','=',3)

	        ->where('r.status','=',2)

	        ->where('r.refund_type','=','101.02')

	        ->when($startTime ?? 0, function ($query) use ($startTime) {

	            return $query->whereTime('deal_time', '>=', $startTime);

	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {

	            return $query->whereTime('deal_time', '<=', $endTime);

	        },function ($query) use ($endTime) {

	            return $query->whereMonth('deal_time');

	        })

	        ->find();

	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');


		//退款单中的疤痕业绩	--不含店转的业绩  --02
		$refund = Db::name('shop_refund_order')->alias('r')

	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

	        //->field('sum(g.pay_price * r.coefficient) as pay_amount')
			->field('sum(g.pay_price) as pay_amount')
	        ->where('r.shop_id','=',$shopId)

	        ->where($wheret)

	        ->where('g.refund_mode','<>','06')

	        ->where('r.type','=',1)

	        ->where('r.status','=',2)

	        ->where('r.refund_type','=','101.02')

	        ->when($startTime ?? 0, function ($query) use ($startTime) {

	            return $query->whereTime('deal_time', '>=', $startTime);

	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {

	            return $query->whereTime('deal_time', '<=', $endTime);

	        },function ($query) use ($endTime) {

	            return $query->whereMonth('deal_time');

	        })

	        ->find();

	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');


		//门诊转店到 门店的 通过门店的归属ID 查询	--收款单 --03
		$receive_zd =Db::name('shop_refund_order')

		    ->field('sum(pay_amount * coefficient) as pay_amount,id')

			->where($where)

		    ->where('referral_shop_id','=',$shopId)

		    ->where('type','=',3)

		    ->where('status','=',2)

		    ->where('refund_type','=','101.02')

		    ->when($startTime ?? 0, function ($query) use ($startTime) {

		        return $query->whereTime('deal_time', '>=', $startTime);

		    })

		    ->when($endTime ?? 0, function ($query) use ($endTime) {

		        return $query->whereTime('deal_time', '<=', $endTime);

		    },function ($query) use ($endTime) {

		        return $query->whereMonth('deal_time');

		    })->find();


		//退款单是店转的 疤痕/项目业绩查询  --04
		$refund_dz = Db::name('shop_refund_order')->alias('r')

	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

	        ->field('sum(g.pay_price * r.coefficient) as pay_amount')

	        ->where($wheret)

	        ->where('r.shop_id','=',$shopId)

	        ->where('g.refund_mode','=','06')

	        ->where('r.type','=',1)

	        ->where('r.status','=',2)

	        ->where('r.refund_type','<>','101.03')

	        ->when($startTime ?? 0, function ($query) use ($startTime) {

	            return $query->whereTime('deal_time', '>=', $startTime);

	        })

	        ->when($endTime ?? 0, function ($query) use ($endTime) {

	            return $query->whereTime('deal_time', '<=', $endTime);

	        },function ($query) use ($endTime) {

	            return $query->whereMonth('deal_time');

	        })

	        ->find();

	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');


		//收款单是店转的 疤痕/项目业绩---05
		$receive_dz = Db::name('shop_refund_order')->alias('r')

	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

	        ->field('sum(g.pay_price * r.coefficient) as pay_amount')

	        ->where('r.shop_id','=',$shopId)

	        ->where($wheret)

	        ->where('g.refund_mode','=','06')

	        ->where('r.type','=',3)

	        ->where('r.status','=',2)

	        ->where('r.refund_type','=','101.02')

	        ->when($startTime ?? 0, function ($query) use ($startTime) {

	            return $query->whereTime('deal_time', '>=', $startTime);

	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {

	            return $query->whereTime('deal_time', '<=', $endTime);

	        },function ($query) use ($endTime) {

	            return $query->whereMonth('deal_time');

	        })

	        ->find();

	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');


		//门诊收款单 是非疗程的 疤痕/项目业绩  --06
		$receive_mz =Db::name('shop_refund_order')

		    ->field('sum(pay_amount * coefficient) as pay_amount')

			->where($where)

		    ->where('shop_id','=',$shopId)

			->where('is_treatment','=',2)

		    ->where('type','=',3)

			->where('referral_shop_id','>',0)

		    ->where('status','=',2)

		    ->where('refund_type','<>','101.03')

		    ->when($startTime ?? 0, function ($query) use ($startTime) {

		        return $query->whereTime('deal_time', '>=', $startTime);

		    })

		    ->when($endTime ?? 0, function ($query) use ($endTime) {

		        return $query->whereTime('deal_time', '<=', $endTime);

		    },function ($query) use ($endTime) {

		        return $query->whereMonth('deal_time');

		    })->find();


			//实收金额 - 退款金额 + 门诊收钱分给店面的一半金额
			return round(($receive['pay_amount']-$refund['pay_amount'] +$receive_zd['pay_amount']+$receive_dz['pay_amount']-$refund_dz['pay_amount']),2);
	}


	public function sumShopPayMoneyNewMZ($scene,$shopId,$startTime,$endTime){
		switch ($scene) {
			case 'XM':

		        $where['r.order_type']=2;
				$where['r.business_type']=2;
				break;

		    case 'BH':

		        $where['r.order_type']=2;
				$where['r.business_type']=1;
				break;

		    default:

		        break;

		}

		//收款单 收款类型是 收款 且不含收款方式不是店转的
		$receive = Db::name('shop_refund_order')->alias('r')

		    ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

		    // ->field('sum(r.pay_amount * r.coefficient) as pay_amount')
		    ->where($where)
		    ->where('r.shop_id','=',$shopId)

		    ->where('g.refund_mode','<>','06')

		    ->where('r.type','=',3)

		    ->where('r.status','=',2)

		    ->where('r.refund_type','=','101.02')

		    ->when($startTime ?? 0, function ($query) use ($startTime) {

		        return $query->whereTime('deal_time', '>=', $startTime);

		    })

		    ->when($endTime ?? 0, function ($query) use ($endTime) {

		        return $query->whereTime('deal_time', '<=', $endTime);

		    },function ($query) use ($endTime) {

		        return $query->whereMonth('deal_time');

		    })

		    // ->find();

		    ->column('r.pay_amount as pay_amount','r.id');
			//->column('r.pay_amount * r.coefficient as pay_amount','r.id');

		//var_dump($receive);
		//退款单
		$refund = Db::name('shop_refund_order')->alias('r')

		    ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')

		    // ->field('sum(r.pay_amount * r.coefficient) as pay_amount')

		    ->where('r.shop_id','=',$shopId)

		    ->where('g.refund_mode','<>','06')

		    ->where('r.type','=',1)

		    ->where('r.status','=',2)

		    ->where('r.refund_type','=','101.02')

		    ->when($startTime ?? 0, function ($query) use ($startTime) {

		        return $query->whereTime('deal_time', '>=', $startTime);

		    })

		    ->when($endTime ?? 0, function ($query) use ($endTime) {

		        return $query->whereTime('deal_time', '<=', $endTime);

		    },function ($query) use ($endTime) {

		        return $query->whereMonth('deal_time');

		    })

		    // ->find();

		    //->column('r.pay_amount * r.coefficient as pay_amount','r.id');
			->column('r.pay_amount  as pay_amount','r.id');

		// return round($receive['pay_amount'] -$refund['pay_amount'],2);

		$receive_sum = array_sum($receive);

		$refund_sum = array_sum($refund);

		return bcsub($receive_sum,$refund_sum,2);
	}

	// 成交新单顾客数
	public function cj_customer($shop_id)
	{

	    $where = [];


	    array_push($where,['cj_time','>=',1672506061]);
	    array_push($where,['cj_money','>=','500.00']);

	    array_push($where,['final_consumption|final_service','>','0']);

	    array_push($where,['shop_id','=',$shop_id]);


	    $cjCustomerIds = Db::name('customer')->where($where)->column('id');


	    return $cjCustomerIds;

	}

	// 流失顾客 ps:3个月内有消费记录或是服务记录;
	public function baoyou_customer($ids,$date)
	{

	    $tagTime = strtotime($date.' -3 month');

	    $where = [];

	    // array_push($where,['cj_time','>','0']);
	    // array_push($where,['cj_money','>=','500.00']);

	    // array_push($where,['shop_id','=',$shop_id]);

	    array_push($where,['id','in',$ids]);
	    array_push($where,['final_consumption|final_service','>',$tagTime]);

	    $customer_list = Db::name('customer')->where($where)->column('id');

	    // return count($customer_list);
	    return $customer_list;

	}


	public function setmenzhenclients(){

		echo '国展转门诊客户';echo '<br/>';


		/* $start_time = strtotime('2022-01-01 00:00:00');
		$end_time = strtotime('2022-12-31 23:59:59'); */

		$where = [];


		//$shop_list = [73782775201354];

		/* $qingchunddou = [10];
		$bahen = [1,2,3,4,5,6,7,8];
		$pifumeirong = [9,11,12]; */

		//array_push($where,['create_time','>=',$start_time]);
		//array_push($where,['create_time','<=',$end_time]);
		//array_push($where,['shop_id','in',$shop_list]);
		//array_push($where,['creator_id','in',[520,1646,522,518]]);
		//array_push($where,['operator','=',1646]);
		array_push($where,['shop_id','=',79154893551569]);

		$customer_list = Db::name('customer')->where($where)
		// ->count();
		// ->limit(10000,5000)
		->column('id,customer_name,telephone,fcustId,king_no');


		echo count($customer_list);echo '<br/>';//exit;

		echo '<pre>';
		print_r($customer_list);exit;

		//$where = [];
		//array_push($where,['customer_id','=',$val['id']]);

		/* $customerservice = Db::name('service')->where($where)
		->column('id,shop_id,customer_id'); */

		/* $customerBanlance = Db::name('customer_balance')->where($where)
		->column('id,shop_id,customer_id');

		echo count($customerBanlance);echo '<br/>'; */
		// echo '<pre>';
		// print_r($customer_list);
		//exit;

		//$num = 0;
		foreach ($customer_list as $key => $val)
		{


			// $wheree = [];

			// //$shop_listw = [73782775206631];
			// array_push($wheree,['shop_id','=',73782775206631]);
			// array_push($wheree,['telephone','=',$val['telephone']]);
			// $customerMenzhen = Db::name('customer')->where($wheree)
			// ->column('id,customer_name,telephone,shop_id');

			// //echo $customerMenzhen[0]['id'];echo '<br/>';

			// $wheres = [];

			// array_push($wheres,['customer_id','=',$val['id']]);

			// $customerservice = Db::name('service')->where($where)
			// ->column('id,shop_id,customer_id');

			// echo '<pre>';
			// print_r($customerservice);



			$saves=[

				 'fcustId'=>'',
				 'king_no'=>'',

				];

			$res = Db::name('customer')->where('id','=',$val['id'])->update($saves);





		}
		//echo $num;
		exit;





	}


	public function setShopListlianying(){

		//echo '联营门店书店导出';exit;


		//美团  118 50
		//抖团  609
		//抖音  54 55
		//外联 46，47  转介绍 49

		// $startTime  = $_GET['start_time'];
		// $endTime  = $_GET['end_time'];

		// $start_time = strtotime($startTime);
		// $end_time = strtotime($endTime)+86399;

		$xianshang = [43,44,50,51,52,54,55,118,584,609];

		// $shopIds = Db::name('shop')->where('type','=',3)->column('shop_id');

		//$shopIds =[73782774859952,76520948050011];
		$where = [];
		$start_time = '1719763200';
		$end_time = '1722355200';
		array_push($where,['shop_id','<>',74076159037951]);
		// array_push($where,['shop_id','in',$shopIds]);

		array_push($where,['source_id','in',$xianshang]);
		//array_push($where,['create_time','between',1719763200,1722355200]);
		//array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
		array_push($where,['create_time','between',"$start_time,$end_time"]);

		/* $xsyyAllList = Db::name('customer')->where($where)->column('id,shop_id,customer_name,telephone,first_time,second_time,source_id,cj_money');

		echo count($xsyyAllList);

		echo '<pre>';
		print_r($xsyyAllList); */




		//$biaoqianCode = $_POST['laiyuan'];

		//$tagName = Db::name('setting')->where('id',$biaoqianCode)->value('name');

		//$where = [];



		//array_push($where,['member_label','like','%'.$biaoqianCode.'%']);



		$customer_list = Db::name('customer')->where($where)
		->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,creator_id,operator,nurse_id,remarks,first_time,second_time,effective_entry,cj_time,consumption,member_label,cj_money,membership_leve,be_overdue_time,introducer');
		
		//echo count($customer_list);exit;
		
		// echo "<pre>";
		// print_r($customer_list);
		// echo "</pre>";
		// exit;

		$shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		// $nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

		// $introducer = array_values(array_unique(array_column($customer_list,'introducer')));


		$where = [];

		array_push($where,['shop_id','in',$shop_ids]);

		$shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


		// $where = [];

		// array_push($where,['id','in',$nurse_id]);

		// $nurseList = Db::name('shop_person')->where($where)->column('name','id');

		// $where = [];

		// array_push($where,['id','in',$introducer]);

		// $introducerList = Db::name('shop_person')->where($where)->column('name','id');

		
		// 疤痕类型数组
		$where = [];

		array_push($where,['category_id','=',1]);

		$scarType = Db::name('setting')->where($where)->column('name','id');

		// 疤痕部位数组
		$where = [];

		array_push($where,['category_id','=',2]);

		$positionArr = Db::name('setting')->where($where)->column('name','id');

		// 来源数组
		$where = [];

		array_push($where,['category_id','=',4]);

		$laiyuanList = Db::name('setting')->where($where)->column('name','id');

		// 性别数组
		$sexList = [0=>'未知',1=>'男',2=>'女'];

		// 会员数组
		$vipList = [1=>'白玉兰',2=>'紫玉兰',3=>'金玉兰'];

		// 客服数组
		$kefuList = Db::name('admin_user')->column('realname','id');
		
		$personList     = Db::name('shop_person')->column('name', 'id');

		$list = [];

		foreach($customer_list as $key => $val){

		    $scarInfo = Db::name('customer_scar')->where('customer_id',$val['id'])->select()->toArray();

		    // $customer_setting_id_type = $scarInfo[0]['customer_setting_id_type'] ?? "";

		    $one = [];

		    $one['customer_name'] = $val['customer_name'];

		    $one['telephone'] = $val['telephone'];
			
			$one['sex']             = $sexList[$val['sex']] ?? '未知';
			
			$one['operatorN'] = $kefuList[$val['operator']] ?? '';
			
			$one['nurse_name'] = $personList[$val['nurse_id']] ?? '';
	
		    $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

		    $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

		    $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d',$val['create_time']) : '';

		    $one['effective_entry'] = $val['effective_entry'] == 1 ? '是' : '否';

		    $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d ',$val['first_time']) : '';

		    $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d ',$val['second_time']) : '';

		    $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d ',$val['cj_time']) : '';

		    $one['cj_money'] = $val['cj_money'];
			
			$one['remarks'] = $val['remarks'];


		    $scarStr = $scarInfo[0]['customer_setting_id_type'] ?? "";

		    $positionStr = $scarInfo[0]['customer_setting_id_position'] ?? "";

		    if($scarStr){

		        $one['customer_scar'] = $scarType[$scarStr] ?? '';

		    }else{

		        $one['customer_scar'] = '';

		    }


		    if($positionStr){

		        $one['customer_position'] = $positionArr[$positionStr] ?? '';

		    }else{

		        $one['customer_position'] = '';

		    }

		    array_push($list,$one);
		}

		// echo '<pre>';
		// print_r($list);exit;

		$callName = [
		
			 'create_time'    =>'录入时间',
			
		    'customer_name'   =>'姓名',

		    'shop_name' =>'门店',

		    

		   //'sex' =>'性别',

		    'telephone'     =>'手机号',

		    'source_name'   =>'来源',

		   

			'operatorN' =>'客服',
			
			//'nurse_name' =>'护理师',
			
		    //'effective_entry'    =>'是否有效进店',

		    'first_time'    =>'首次进店',

		    'second_time'    =>'二次进店',


		    //'cj_money'    =>'成交金额',



		 //    'customer_scar' => '疤痕类型',
			
			// 'customer_position' => '部位',
			
			'remarks' => '备注',


		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('联营门店数据导出',$callName,$list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";

	}

	//门店成交新单客户 的会员生日 数据核对
	public function setcustomerCaseList(){
		//echo '优案导出';

		$starttime =strtotime($_GET['cjtime']);

		$where = [];


		array_push($where,['cj_time','>=',$starttime]);
		array_push($where,['cj_money','>=','500.00']);

		array_push($where,['final_consumption|final_service','>','0']);


		$cjCustomerInfos = Db::name('customer')->where($where)->column('id,customer_name,telephone,birthday,nurse_id,shop_id,cj_time,cj_money,create_time');



		$shopList = Db::name('shop')->column('shop_name','shop_id');

		$shop_personList = Db::name('shop_person')->column('name','id');

		$list = [];

		foreach($cjCustomerInfos as $key => $val){


		    $one = [];

		    $one['customer_name'] = $val['customer_name'];

		    $one['telephone'] = $val['telephone'];

		    $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

			$one['nurse_id'] = $shop_personList[$val['nurse_id']] ?? '';

			if($val['birthday']!=0){
				$one['birthday'] = date('m-d', $val['birthday']);
			}else{
				$one['birthday']= '';
			}

		    $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

		    $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d H:i:s',$val['cj_time']) : '';

		    $one['cj_money'] = $val['cj_money'];



		    array_push($list,$one);
		}

		/* echo '<pre>';
		print_r($list);exit;

		 */
		$callName = [

		    'customer_name'   =>'姓名',

		    'telephone'     =>'手机号',

		    'shop_name'   =>'门店',

		    'nurse_id'    =>'护理师',

		    'birthday'    =>'会员生日',

		    'create_time'    =>'录入时间',

		    'cj_time'    =>'成交时间',

		    'cj_money'    =>'成交金额',

		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('成交客户会员生日数据导出',$callName,$list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


	}

	//导出未成交客户
	public function setCustomerListDC(){

		echo '数据导出---';

		$shopList = Db::name('shop')->column('shop_name','shop_id');
        // //$tagTime = '0';
        // $tagTime = strtotime('2023-01-01');
		// $where = []; 
        // // array_push($where,['cj_time','>',$tagTime]);
        // array_push($where,['cj_money','>=','500.00']);

       	// array_push($where,['final_consumption','=','0']);
		// array_push($where,['final_service','=','0']);

		//array_push($where,['id','=','37401']);

        // array_push($where,['shop_id','in',$shopIds]);

        // array_push($where,['shop_id','not in',$mShopIds]);

        

        // echo "<pre>";
        // print_r($where);
        // echo "</pre>";
        // exit;

        // $cjCustomerIds = Db::name('customer')->alias('c')
        //                 ->join('customer_scar cs', 'cs.customer_id=c.id', 'LEFT')
        //                 ->where($where)
        //                 ->group('c.id')
        //                 ->column('c.id');

		// $costomerList = Db::name('customer')->where($where)->column('id,cj_money,cj_time');

		// echo count($costomerList);

		// foreach($costomerList as $key => $val){

		// 	$saveTJ=[];
		// 	$saveTJ=[

		// 			'final_consumption'=>$val['cj_time'],

		// 		];

		// 	//Db::name('customer')->where('id','=',$val['id'])->update($saveTJ);
		// }



		// echo "<pre>";
        // print_r($costomerList);
        // echo "</pre>";
       // exit;

		//2年内 消费金额大于等于 10000以上的客户
		// $where = [];
		// array_push($where,['cj_time','>=',1672502400]);
		// array_push($where,['cj_time','<',1693497600]);
		// //array_push($where,['consumption','>=',10000]);
		
		// $costomerList = Db::name('customer')->where($where)->column('id,customer_name,telephone,shop_id,sex,birthday,age,nurse_id,cj_money,cj_time');

		//echo count($costomerList);exit;
		

		 
		$where = []; 
		
		$start_time =1682870400;
		$end_time =1712937600;
		// array_push($where,['member_classification','=',73]);
		 //array_push($where,['fcustId','>',0]); 
		
		//array_push($where,['cj_time','>=',1712505600]); //时间戳时 2023-01-01
		
		//array_push($where,['cj_time','<=',1712937600]); //时间戳时 2023-01-01
		//array_push($where,['source_id','=',49]);
		// array_push($where,['consumption','<=',2000]);
		//array_push($where,['create_time','>=',1640966400]); //时间戳时 2023-01-01
		//array_push($where,['first_time','>=',0]);
		//array_push($where,['cj_money','>=',500]);
		array_push($where,['shop_id','in',[79154893551569]]); //1680278400
		//array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
		
		//$costomerList = Db::name('customer')->where($where)->column('id,customer_name,telephone,sex,age,shop_id,fcustId,king_no,nurse_id,first_time,second_time,final_consumption,final_service,consumption,cj_time,cj_money');
		$costomerList = Db::name('customer')->where($where)->column('id,customer_name,telephone,sex,age,member_label,shop_id,fcustId,king_no,nurse_id,first_time,second_time,final_consumption,final_service,consumption,cj_time,cj_money');
		//$costomerList = Db::name('customer')->where($where)->column('id,customer_name,telephone,sex,birthday,age,nurse_id,shop_id,cj_money,cj_time');

		//echo count($costomerList);exit;
		// echo '<pre>';exit;
		// print_r($costomerList);exit;

		$sexArr         = ['0' => '未知', '1' => '男', '2' => '女'];

		 //获取员工数据
		 $personList     = Db::name('shop_person')->column('name', 'id');
		$list = [];

		foreach($costomerList as $key => $val){


		    $one = [];

		    $one['customer_name'] = $val['customer_name'];

		    $one['telephone'] = $val['telephone'];

			// if($val['shop_id'] == '73782774813357'){
			// 	$one['shop_name'] = '文化路';
			// }

			// if($val['shop_id'] == '73782774813542'){
			// 	$one['shop_name'] = '航海路';
			// }
			$one['sex']             = $sexArr[$val['sex']] ?? '未知';
			$one['shop_name'] = $shopList[$val['shop_id']] ?? '';
			$one['cj_money'] = $val['cj_money'];
			 $one['age'] = $val['age'];

		    $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d',$val['first_time']) : '';

			$one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d',$val['second_time']) : '';

		    $one['final_consumption'] = $val['final_consumption'] > 0 ? date('Y-m-d',$val['final_consumption']) : '';

		   $one['final_service'] = $val['final_service'] > 0 ? date('Y-m-d',$val['final_service']) : '';

		   $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d',$val['cj_time']) : '';


			//$one['birthday']       = $val['birthday'] ? date('Y-m-d', $val['birthday']) : '';

			$one['consumption'] = $val['consumption'];
			$one['member_label'] = $val['member_label'];
			
			$one['fcustId'] = $val['fcustId'];
			
			$one['king_no'] = $val['king_no'];
			
			$one['nurse_name']      = $personList[$val['nurse_id']] ?? '';

		    array_push($list,$one);
		}

		// echo "<pre>";
  //       print_r($list);
  //       echo "</pre>";
  //      	exit;


		$callName = [

		    'customer_name'   =>'姓名',

			'age'   =>'年龄',

			'sex'   =>'性别',

			//'birthday'   =>'生日',

			'shop_name'   =>'门店',

			'nurse_name'     =>'护理师',
			'first_time'   =>'首次进店',

			'second_time'   =>'二次进店',

			// 'final_consumption'   =>'消费时间',

			// 'final_service'   =>'服务时间',
			
			'member_label'   =>'客户标签',

		    'telephone'     =>'手机号',

			 'cj_time'     =>'成交时间',

			 'cj_money'     =>'成交金额',
			 
			 'fcustId'     =>'客户编码',
			 
			 'king_no'     =>'K3代码',

			// 'consumption'     =>'累计消费金额',

		];

		//$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('23年进店未成交客户档案',$callName,$list,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";



	}


	//直管门店 每天数据导出报表  

	public function getZGshopCustomerList(){

		//echo '直管门店业绩数据和各项疤痕数据导出'; echo '<br/>';

		$startTime  = $_GET['start_time'];
		$endTime  = $_GET['end_time'];

		$start_time = strtotime($startTime);
		$end_time = strtotime($endTime)+86399;

		// echo $start_time;echo '<br/>';
		// echo $end_time;echo '<br/>';
		$where = []; 
		//$shopIDS = [73782774859952,73782776396518,76520948050011,78808830816801,78852178480703,78870916600002,78872028809917,79085556716103,79154893551569,79234669613042,79958737536305];
		array_push($where,['type','<>',2]);
		array_push($where,['status','=',1]);
		array_push($where,['shop_type','=',2]);
		$shopList = Db::name('shop')->where($where )->column('shop_name,shop_id');
		
		// echo '<pre>';
		// print_r($shopList);exit;
		
		
		$bhList = [1,2,3,4,5,6,7,8,655,656];
		$xmList = [9,11,12];
		$ddList = [10];

		$xianshang = [43,44,50,51,52,54,55,118,584,609];
		
		// foreach($shopList as $key => $val){
			
			
		// 	// 疤痕成交个数  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','in', $bhList ]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	$customerBHCJList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('c.id,c.customer_name,c.shop_id');
						
						
		// 	// 疤痕成交金额  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','in', $bhList ]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	array_push($where,['c.cj_time','>', 0]);
		// 	$customerBHCJAmountList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('sum(c.cj_money) as money');
			
		// 	// echo '<pre>';
		// 	// print_r($customerBHCJAmountList);
			
		// 	// 项目成交个数  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','in', $xmList ]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	$customerXMCJList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('c.id,c.customer_name,c.shop_id');
						
		// 	// 项目成交金额  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','in', $xmList ]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	array_push($where,['c.cj_time','>', 0]);
		// 	$customerXMCJAmountList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('sum(c.cj_money) as money');
			
			
		// 	// 痘痘成交  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','=', 10]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	$customerDDCJList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('c.id,c.customer_name,c.shop_id');
			
			
		// 	// 痘痘成交金额  统计
		// 	$where = [];
		// 	array_push($where,['c.cj_time','>=',$start_time]);
		// 	array_push($where,['c.cj_time','<=',$end_time]);
		// 	array_push($where,['s.customer_setting_id_type','=', 10 ]);
		// 	array_push($where,['c.shop_id','=', $val['shop_id']]);
		// 	array_push($where,['c.source_id','=', 118]);
		// 	array_push($where,['c.cj_time','>', 0]);
		// 	$customerDDCJAmountList= Db::name('customer')->alias('c')
			
		// 	            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
		// 				->where($where)
			
		// 				->column('sum(c.cj_money) as money');
			
			
			
		// 	$shopList[$key]['bhJDNum'] = count($customerBHCJList); //疤痕成交个数
		// 	$shopList[$key]['xmJDNum'] = count($customerXMCJList); //项目成交个数
		// 	$shopList[$key]['ddJDNum'] = count($customerDDCJList); //痘痘成交个数
			
		// 	$shopList[$key]['bhcjAmount'] = $customerBHCJAmountList[0]; //疤痕成交个数
		// 	$shopList[$key]['xmcjAmount'] = $customerXMCJAmountList[0]; //疤痕成交个数
		// 	$shopList[$key]['ddcjAmount'] = $customerDDCJAmountList[0]; //疤痕成交个数
			
		// }
		
		
		// // echo '<pre>';
		// // print_r($shopList);exit;
		// $callName = [
		
		//     'shop_name'   =>'门店',
		
		//     'bhJDNum'   =>'疤痕成交',
		
		//     'bhcjAmount'    =>'疤痕成交金额',
		
		//     'xmJDNum'    =>'项目成交',
		
		//     'xmcjAmount'    =>'项目成交金额',
		
		//     'ddJDNum'    =>'痘痘成交',
			
		// 	 'ddcjAmount'    =>'痘痘成交金额',
		
		    
		
		// ];
		
		// $timeStr = date('Y-m-d',time());
		
		// $downUrl = exportExcelData('美团成交数据',$callName,$shopList,'xlsx',1);
		
		// echo "<script language='javascript'>";
		
		// echo " location='".$downUrl."';";
		
		// echo "</script>";
		
		
		
		// exit;
		
		//查询各个门店 的疤痕 痘痘 项目进店数据
		foreach($shopList as $key => $val){
				
			// 疤痕进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', $bhList ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerBHList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');
			
			// 项目进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', $xmList ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerXMList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id');

			// 疤痘进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', [1,2,3,4,5,6,7,8,10] ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			array_push($where,['source_id','in', $xianshang]);
			$customerBDList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');
			

			// 痘痘进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', $ddList ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerDDList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');
			
			
			
			// 疤痕成交  统计
			$where = [];
			array_push($where,['c.cj_time','>=',$start_time]);
			array_push($where,['c.cj_time','<=',$end_time]);
			array_push($where,['s.customer_setting_id_type','in', $bhList ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerBHCJList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');

			// 项目成交  统计
			$where = [];
			array_push($where,['c.cj_time','>=',$start_time]);
			array_push($where,['c.cj_time','<=',$end_time]);
			array_push($where,['s.customer_setting_id_type','in', $xmList ]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerXMCJList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');

			// 痘痘成交  统计
			$where = [];
			array_push($where,['c.cj_time','>=',$start_time]);
			array_push($where,['c.cj_time','<=',$end_time]);
			array_push($where,['s.customer_setting_id_type','=', 10]);
			array_push($where,['c.shop_id','=', $val['shop_id']]);
			$customerDDCJList= Db::name('customer')->alias('c')

			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')

						->where($where)

						->column('c.id,c.customer_name,c.shop_id');
			
			//美团进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','in', [50,118]]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerMTList= Db::name('customer')->where($where)->column('id');

			//抖音进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 54]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerDYList= Db::name('customer')->where($where)->column('id');

			//抖团进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 609]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerDTList= Db::name('customer')->where($where)->column('id');

			//门头进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 47]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerMETList= Db::name('customer')->where($where)->column('id');

			//外联进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 46]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerWLList= Db::name('customer')->where($where)->column('id');

			//地推进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 48]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerDDTList= Db::name('customer')->where($where)->column('id');

			//转介绍进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','=', 49]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerZJSList= Db::name('customer')->where($where)->column('id');

			//总进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			array_push($where,['source_id','in', $xianshang]);
			array_push($where,['shop_id','=', $val['shop_id']]);
			$customerZList= Db::name('customer')->where($where)->column('id');





			
			$shopList[$key]['bhJDNum'] = count($customerBHList);
			$shopList[$key]['xmJDNum'] = count($customerXMList);
			$shopList[$key]['ddJDNum'] = count($customerDDList);

			$shopList[$key]['bhCJNum'] = count($customerBHCJList);
			$shopList[$key]['xmCJNum'] = count($customerXMCJList);
			$shopList[$key]['ddCJNum'] = count($customerDDCJList);

			$shopList[$key]['mtJDNum'] = count($customerMTList);
			$shopList[$key]['dyJDNum'] = count($customerDYList);
			$shopList[$key]['dtJDNum'] = count($customerDTList);

			$shopList[$key]['mentJDNum'] = count($customerMETList); //门头进店
			$shopList[$key]['wlJDNum'] = count($customerWLList); //外联进店
			$shopList[$key]['dtuiJDNum'] = count($customerDDTList); //外联进店

			$shopList[$key]['zjsJDNum'] = count($customerZJSList); //转介绍进店  

			$shopList[$key]['bdJDNum'] = count($customerBDList); //线上疤痘进店  

			$shopList[$key]['zJDNum'] = count($customerZList); //线上总进店


		}

		

		// echo '<pre>';
		// print_r($shopList);exit;
		$callName = [

		    'shop_name'   =>'门店',

		    'bhJDNum'     =>'疤痕进店',

		    'bhCJNum'   =>'疤痕成交',

		    'xmJDNum'    =>'项目进店',

		    'xmCJNum'    =>'项目成交',

		    'ddJDNum'    =>'痘痘进店',

		    'ddCJNum'    =>'痘痘成交',

		    'mtJDNum'    =>'美团进店',

			'dyJDNum'    =>'抖音进店',

			'dtJDNum'    =>'抖团进店',

			'mentJDNum'    =>'门头进店',

			'wlJDNum'    =>'外联进店',

			'dtuiJDNum'    =>'地推进店',

			'zjsJDNum'    =>'转介绍进店',

			'bdJDNum'    =>'线上疤痘进店',

			'zJDNum'    =>'线上总进店',


		];

		$timeStr = date('Y-m-d',time());

		$downUrl = exportExcelData('直管门店客户进店类型和成交数据表',$callName,$shopList,'xlsx',1);

		echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";


	}




    public function exportCustomerDataOnMonth()
    {
        $param = request()->param();
        $reserveandLost = new ReserveandLost();
        $shopModel = new ShopModel();
        $sTime = strtotime('2022-01-01');
        $start_time = strtotime($param['start_time']);
        $end_time = strtotime($param['end_time'])+24*60*60-1;

        $old_start_time=strtotime(date("Y-m-01 00:00:00",strtotime("-4 month",$start_time)));// 新单成交  开始时间
        $old_end_time=strtotime(date('Y-m-01', $old_start_time) . ' 1 month -1 day'. ' 23:59:59');//新单成交 成交结束时间

        $today = date('Y-m-d');
        $shop = ShopModel::where([
            ['status','=',1],
            ['type','<>',2],
        ])->column('shop_id,shop_name,shop_type,market_id');
        $shopIds= array_column($shop,'shop_id');
        $cj_customer = $reserveandLost->cj_customer($shopIds,[]);

        $today = date('Y-m-d');
        // 保有顾客数

        $tagTime = strtotime($today.' -3 month');

        $where = [];

        array_push($where,['id','in',$cj_customer]);
        array_push($where,['final_consumption|final_service','>',$tagTime]);

        $customer_list = Db::name('customer')->where($where)->group('shop_id')->column('count(*)','shop_id');

        $cj_list=Db::name('customer')->where([['shop_id', 'in', $shopIds], ['member_classification', 'in', [71,72,73]],['membership_leve','=',0],['final_consumption','between',"$start_time,$end_time"], ['consumption', '>=', '5000'], ['create_time','>=',$sTime]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $cj_num=array_column($cj_list,null,'shop_id');

        $xz_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shopIds], ['join_time', 'between', "$start_time,$end_time"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xz_huiyuan_num=array_column($xz_huiyuan_list,null,'shop_id');


        $xianshang = [44,50,51,52,54,55,118,584,609,597];
        $where = [];

        array_push($where,['create_time','>=',$start_time]);

        array_push($where,['create_time','<=',$end_time]);

        array_push($where,['shop_id','in',$shopIds]);

        array_push($where,['source_id','in',$xianshang]);

        $xsyyAll = Db::name('customer')->where($where)->group('shop_id')->column('count(id)','shop_id');

        $where = [];

        array_push($where,['first_time','>=',$start_time]);

        array_push($where,['first_time','<=',$end_time]);


        $whereOr = [];

        array_push($whereOr,['second_time','>=',$start_time]);

        array_push($whereOr,['second_time','<=',$end_time]);

        $mdjdAll = Db::name('customer')

            ->where([['source_id','in',$xianshang]])

            ->where([['shop_id','in',$shopIds]])

            ->where(function($query) use ($where,$whereOr) {

                $query -> where(function($query) use ($where) {

                    $query -> where($where);

                });

                $query -> whereOr(function($query) use ($whereOr) {

                    $query -> where($whereOr);

                });

            })
            ->group('shop_id')->column('count(id)','shop_id');

        // 白玉兰会员数
        $xzw_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shopIds], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "1"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzw_huiyuan_num=array_column($xzw_huiyuan_list,null,'shop_id');


        // 紫玉兰会员数
        $xzp_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shopIds], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "2"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzp_huiyuan_num=array_column($xzp_huiyuan_list,null,'shop_id');


        // 金玉兰会员数
        $xzg_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shopIds], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "3"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzg_huiyuan_num=array_column($xzg_huiyuan_list,null,'shop_id');

        $where = [];

        array_push($where,['cj_time','>=',$start_time]);

        array_push($where,['cj_time','<=',$end_time]);

        array_push($where,['shop_id','in',$shopIds]);

        array_push($where,['source_id','in',$xianshang]);

        $cjAllList = Db::name('customer')->where($where)->group('shop_id')->column('count(id) as num,sum(cj_money) as money','shop_id');

        $where = [];

        array_push($where,['o.deal_time','>=',$start_time]);   //成交时间

        array_push($where,['o.deal_time','<=',$end_time]);

        array_push($where,['o.status','=',2]);    // 推送状态 1是成功

        array_push($where,['o.type','=',3]);   //收款单

        array_push($where,['o.order_type','=',1]);   //收款单--新单

        array_push($where,['c.shop_id','in',$shopIds]);

        array_push($where,['c.source_id','in',$xianshang]);

        $dycjAllList = Db::name('customer')->alias('c')

            ->join('shop_refund_order o', 'c.id=o.uid', 'LEFT')

            ->where($where)

            ->group('c.shop_id')

            ->column('sum(total_amount) as money','c.shop_id');

        foreach ($shop as $key =>$value)
        {
            $shopIds = [];
            array_push($shopIds,$value['shop_id']);
            $cj_customer = $reserveandLost->cj_customer($shopIds,[]);
            $shop[$key]['baoyou_customer']=$customer_list[$value['shop_id']]??0;
            $one['cj_num'] = $cj_num[$value['shop_id']]['sum'] ?? '0';
            $one['xz_huiyuan_num'] = $xz_huiyuan_num[$value['shop_id']]['sum'] ?? '0'; // 新增会员总数
            $shop[$key]['ruhuilv']=$one['cj_num']==0? '0%' :bcmul(bcdiv($one['xz_huiyuan_num'],$one['cj_num'],10),100,2).'%'; // 入会率;
            $shop[$key]['rate_baoyou']=count($cj_customer)>0 ? (round($shop[$key]['baoyou_customer']/count($cj_customer),4)*100).'%' :'0%'; // 成交保有率
            if($value['shop_type']!=2)
            {
                $shop[$key]['rate_baoyou']='--';
            }
            $xsyyAll[$value['shop_id']]=$xsyyAll[$value['shop_id']]??0;
            $mdjdAll[$value['shop_id']]=$mdjdAll[$value['shop_id']]??0;
            $jindianlv = $xsyyAll[$value['shop_id']] > 0 ? bcdiv($mdjdAll[$value['shop_id']],$xsyyAll[$value['shop_id']],4): 0;

            $shop[$key]['store_entry_rate'] = $jindianlv*100;//进店率

            $shop[$key]['store_entry_rate'] .='%';

            $wherec = [];
            array_push($wherec,['sp_excellent_time','>=',$start_time]);
            array_push($wherec,['sp_excellent_time','<=',$end_time]);
            array_push($wherec,['shop_id','=',$value['shop_id']]);
            array_push($wherec,['selection_results','in',[2,3]]);
            $shopCase = Db::name('case')->where($wherec)->count('id');
            $shop[$key]['caseNum'] = $shopCase;

            $lost_customer = $reserveandLost->lost_customer($cj_customer,$today);

            $shop[$key]['lost_customer'] = count($lost_customer);

            $warn_customer = $reserveandLost->warning_customer('','','back',$shopIds,date('Y-m-d',$start_time));
            $shop[$key]['warn_customer'] = count($warn_customer);

            $shop[$key]['xzw_huiyuan_num'] = $xzw_huiyuan_num[$value['shop_id']]['sum'] ?? '0'; // 新增白玉兰会员

            $shop[$key]['xzp_huiyuan_num'] = $xzp_huiyuan_num[$value['shop_id']]['sum'] ?? '0'; // 新增紫玉兰会员

            $shop[$key]['xzg_huiyuan_num'] = $xzg_huiyuan_num[$value['shop_id']]['sum'] ?? '0'; // 新增金玉兰会员

            $scarList = Db::name('customer')
                ->alias('b')
                ->where('b.cj_time','>=',$old_start_time)//2022.01.01
                ->where('b.cj_time','<=',$old_end_time) //2022.01.31
                ->where('b.cj_money','>',0)
                ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
                ->where('sp.business_type','=',1)
                ->where('sp.type','=',3)
                ->where('sp.shop_id','=',$value['shop_id'])
                ->column('b.id,b.shop_id,b.cj_money,telephone');

            $bahenNum =$shopModel->redAdminProjectByShopId($scarList,$old_start_time,$old_end_time,$end_time);

            $xiangmNum =$shopModel->redAdminXmByShopId($scarList,$old_start_time,$old_end_time,$end_time);

            $shop[$key]['developRate'] =count($scarList)>0 ? (bcdiv($bahenNum,count($scarList),4)*100).'%' :'0%';  //疤痘二次消费率
            $shop[$key]['popularityRate'] =count($scarList)>0 ? (bcdiv($xiangmNum,count($scarList),4)*100).'%' :'0%'; //项目普及率
            $shop[$key]['all_new_order_num'] = $cjAllList[$value['shop_id']]['num']??0;
            $shop[$key]['all_new_order_money'] = $dycjAllList[$value['shop_id']]??0;

            if($value['market_id'] == 8 || $value['market_id'] == 9 || $value['market_id'] == 10|| $value['market_id'] == 11)
            {
                $newsBDMoney = $this->sumShopPayMoneyNewMZ('XM',$value['shop_id'],$start_time,$end_time);
                $budanMoney = $this->sumShopPayMoneyNewMZ('BH',$value['shop_id'],$start_time,$end_time);
            }else{
                $newsBDMoney = $this->sumShopPayMoneyNew('XM',$value['shop_id'],$start_time,$end_time);
                $budanMoney = $this->sumShopPayMoneyNew('BH',$value['shop_id'],$start_time,$end_time);
            }

            $shop[$key]['xmAmount'] = $newsBDMoney;
            $shop[$key]['bhAmount'] = $budanMoney;


        }

        $callName = [

            'shop_name'   =>'门店',

            'baoyou_customer'    =>'保有人数',

            'store_entry_rate'    =>'到店率',

            'ruhuilv'    =>'入会率',

            'rate_baoyou'    =>'保有率',


            'caseNum'    =>'优秀案例',

            'lost_customer'    =>'流失人数',

            'warn_customer'    =>'预警人数',

            'xzw_huiyuan_num'    =>'白玉兰会员',

            'xzp_huiyuan_num'    =>'紫玉兰会员',

            'xzg_huiyuan_num'    =>'金玉兰会员',

            'developRate'    =>'疤痘二次开发率',

            'popularityRate'    =>'项目普及率',

            'all_new_order_num'    =>'新单个数',

            'all_new_order_money'    =>'新单金额',

            'xmAmount'    =>'疤痘补单',

            'bhAmount'    =>'项目补单',


        ];
        $downUrl = exportExcelData(date('Y-m',$start_time).'客户盘点',$callName,$shop,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

	

	//门店累计消费金额数据维护调整
	public function setUpdateClientMoneyList(){

		echo '修改客户累计消费金额总数据';

		
		$where = []; 
		//array_push($where,['cj_money','>',0]);
		//array_push($where,['consumption','>=',1]);
		/* 
		* 更新 2022-02-15-----2022-04-30 
		* 更新 2022-04-30-----2022-06-30 
		 
		 
		 */
		array_push($where,['create_time','<',1672502399]); //时间戳时 2022-05-01
		array_push($where,['create_time','>=', 1664553599]); //时间戳时 2022-10-31
		array_push($where,['first_time','>',0]);
		//array_push($where,['id','=',356442]);

		
		$costomerList = Db::name('customer')->where($where)->column('id,customer_name,telephone,age,consumption,membership_leve');
		
		//echo count($costomerList);exit;


		foreach ($costomerList as $key =>$value)
        {

			//客户收款合计
			$whereO = []; 
			//array_push($whereO,['deal_time','>=',1651334400]);
			array_push($whereO,['type','=',3]);
			array_push($whereO,['status','=',2]); //时间戳时 2022-05-01
			array_push($whereO,['refund_type','<>','101.03']);
			array_push($whereO,['uid','=',$value['id']]);
			$costomerMoeeySK = Db::name('shop_refund_order')->where($whereO)->column('uid,sum(pay_amount * coefficient) as pay_amount');

			//客户退款合计
			$whereT = []; 
			//array_push($whereT,['deal_time','>=',1651334400]);
			array_push($whereT,['type','=',1]);
			array_push($whereT,['status','=',2]); //时间戳时 2022-05-01
			array_push($whereT,['refund_type','<>','101.03']);
			array_push($whereT,['uid','=',$value['id']]);
			$costomerMoeeyTK = Db::name('shop_refund_order')->where($whereT)->column('uid,sum(pay_amount * coefficient) as pay_amount');


			
			// echo '<pre>';
			// print_r($costomerMoeeySK);

			// echo '<pre>';
			// print_r($costomerMoeeyTK);

			$leijiAmount = bcsub($costomerMoeeySK[0]['pay_amount'],$costomerMoeeyTK[0]['pay_amount'],2);
			//echo $leijiAmount;

			if($leijiAmount>='50000'){
				$aa = 73;
				
				if($value['membership_leve']> 0){
					$vip = 3;
				}
				
			}
			if($leijiAmount>='15000' && $leijiAmount< '50000' ){
				$aa = 72;
				if($value['membership_leve']> 0){
					$vip = 2;
				}
			}
			if($leijiAmount>='5000' && $leijiAmount< '15000' ){
				$aa = 71;
				if($value['membership_leve']> 0){
					$vip = 1;
				}
			}
			if($leijiAmount>'0' && $leijiAmount< '5000' ){
				$aa = 70;
				$vip = '0';
			}

			if($leijiAmount =='0'){
				$aa = '';
				$vip = '0';
			}
			
			// if($leijiAmount <'0'){
			// 	$aa = '';
			// 	$vip = '0';
			// }


			$saveTJ=[];
			$saveTJ=[

					'member_classification' => $aa,
					'consumption' => $leijiAmount,
					'membership_leve' => $vip
					

				];
			
			echo '<pre>';
			print_r($saveTJ);//exit;

			Db::name('customer')->where('id','=',$value['id'])->update($saveTJ);


		}


		exit;

	}
	
	//修改和维护服务卡项的 显示金额 
	public function updateservicelistdc(){
		
		echo '数据显示';
		
		$startTime = '1672502400'; //2022-05-01
		$endTime = '1682870400'; //2022-05-01
		
		$where = [];
		array_push($where,['shop_id','=',73782774881835]); //时间戳时 2022-05-01  18636428659
		array_push($where,['create_time','>', $startTime]); //时间戳时 2022-10-31
		//array_push($where,['create_time','>', $endTime]);
		array_push($where,['status','=',1]);
		//array_push($where,['customer_id','=',48143]);
		
		
		$serviceList = Db::name('service')->where($where)->column('id,shop_id,customer_id,goods_id,kk_money,create_time');
		
		echo count($serviceList);exit;
		
		// echo '<pre>';
		// print_r($serviceList);
		
		foreach ($serviceList as $key =>$value)
		{
			$orderList = Db::name('shop_order')->alias('s')
			
			    ->join('shop_order_goods g', 's.id=g.order_id', 'LEFT')
			
			    ->where('s.king_status','=',1)
				
			    //->where('g.is_project','=',2)
				
			    ->where('s.deal_time','>',$startTime)
				//->where('s.deal_time','>',$endTime)
				
				->where('s.uid','=',$value['customer_id'])
				
				->where('g.goods_id','=',$value['goods_id'])
			
			    ->column('s.uid,(g.pay_price * g.number) as money ,g.goods_id,g.goods_name,g.create_time,g.is_project');
				
			
			foreach ($orderList as $k =>$v)
			{
				
				//判断 客户 ID 一致  商品ID一致  开卡时间一致
				
				if($value['customer_id'] ==$v['uid'] && $value['goods_id'] ==$v['goods_id'] && $value['create_time'] ==$v['create_time'] ){
					
					
					$saves = [];
					$saves=[
					
						'kk_money'=>$v['money'],
					
					];
					
					$res = Db::name('service')->where('id','=',$value['id'])->update($saves); 
					echo '服务卡项开卡金额修改成功----'.$res.'------客户ID'.$v['uid'];echo '<br/>';
				}
				
				
			}
			
			/* echo '<pre>';
			print_r($orderList);	
			
			
			if(count($orderList) >= 2){
				echo '数据修改';echo '<br/>';
			}else{
				
			} */
		}
		
		
		
		
	}
	
	
	
	//2023年内拓客户档案数据的信息导出  
	public function setntclientlistinfo(){
		
		$startTime  = $_GET['start_time'];
		$endTime  = $_GET['end_time'];
		
		$start_time = strtotime($startTime);
		$end_time = strtotime($endTime)+86399;
		
		$where  = [];
		//array_push($where,['member_label','like',649]);
		array_push($where,['member_label','like','%649%']);
		
		
		//查询 所有标记 内拓成交 标签的客户档案信息
		$customerList= Db::name('customer')->alias('c')
		
		            ->join('shop s', 's.shop_id=c.shop_id', 'LEFT')
		
					//->where('c.telephone','=',$val[0])
		
					->where($where)
		
					->column('c.id,c.shop_id,c.customer_name,c.telephone,s.shop_name,s.market_name');
		
		//echo count($customerList);
		
		
		
		foreach ($customerList as $key =>$value)
		{
			
			//根据这些客户信息 查询 这些客户在每个月 的服务消耗记录 
			$where = [];
			
			array_push($where,['customer_id','=',$value['id']]);
			array_push($where,['create_time','>=',$start_time]);
			array_push($where,['create_time','<=',$end_time]);
			
			$serviceLog = Db::name('service_log')->where($where)->column('id,service_id');
			
			//根据这些客户信息 查询 这些客户在每个月 的 销售金额 减去 退货金额 等于 消耗金额
			
			$where = [];
			array_push($where,['uid','=',$value['id']]);
			array_push($where,['deal_time','>=',$start_time]);
			array_push($where,['deal_time','<=',$end_time]);
			array_push($where,['status','=',2]);
			
			$orderAmount = Db::name('shop_order')->where($where)->column('id,sum(total_amount) as tamount');
			
			
			$where = [];
			array_push($where,['uid','=',$value['id']]);
			array_push($where,['deal_time','>=',$start_time]);
			array_push($where,['deal_time','<=',$end_time]);
			array_push($where,['status','=',2]);
			array_push($where,['type','=',2]);
			
			$refundorderAmount = Db::name('shop_refund_order')->where($where)->column('id,sum(total_amount) as tamount');
			
			
			
			$customerList[$key]['serviceNum'] = count($serviceLog);
			
			$customerList[$key]['amount'] = bcsub($orderAmount[0]['tamount'],$refundorderAmount[0]['tamount'],2);
			
		}
		
		// echo '<pre>';
		// print_r($customerList);
		
		$callName = [
		
		    'market_name'   =>'市场',
		
		    'shop_name'     =>'门店',
		
		    'customer_name'   =>'客户姓名',
		
		    'telephone'    =>'手机号',
		
		    'serviceNum'    =>'消耗次数',
		
		    'amount'    =>'核销金额',
		
		
		];
		
		$timeStr = date('Y-m-d',time());
		
		$downUrl = exportExcelData('内拓成交的档案信息',$callName,$customerList,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
	}
	
	
	
	public function setThreeClientInfos(){
		
		//echo '3.8活动预约领取信息';
		$start_time = 1682870400;
		$end_time = 1685548799;
		
		$where = [];
		
		$shopIds= $request['shopIds'] ?? '';
		
		
		array_push($where, ['s.status', '=', 1]);//门店是否正常营业
			
		array_push($where, ['s.shop_id', '<>', 74076159037951]);//只有直营店数据
		
		array_push($where, ['s.type', '<>', 2]);//门店是否正常营业
		
		
		$sqlQuery = Db::name('shop')->alias('s')
		
		
		    ->join('shop_market m', 's.market_id = m.id', 'LEFT')
		
		
		    ->where($where);
		
		
		$dataCount = $sqlQuery->count();
		
		
		$arr=[];
		
		if ($dataCount) {
		
		
		    $field = 's.shop_id, s.shop_name,m.name,s.shop_type,s.market_id';
		
		
		    $list = $sqlQuery->field($field)
		
		
		        ->order('m.id desc')
		
		
		        ->select()->toArray();
		
		
		    $arr=array_column($list,null,'shop_id');
			
		    $shop_id=array_column($list,'shop_id');
			
			
			/* ------------------------------------疤痕 青春痘  项目 进店 统计开始-------------------------------------------------------- */
			
			//疤痕 青春痘 项目 来源  
			$bh = [1,2,3,4,5,6,7,8,655];   /* */
			
			
			//疤痕进店
			$where = [];
			
			array_push($where,['c.first_time','>=',$start_time]);
			
			array_push($where,['c.first_time','<=',$end_time]);
			
			$whereOr = [];
			
			array_push($whereOr,['c.second_time','>=',$start_time]);
			
			array_push($whereOr,['c.second_time','<=',$end_time]);
			
			$scarBhJD = Db::name('customer')->alias('c')
			
			            ->join('customer_scar cg', 'cg.customer_id=c.id', 'LEFT')
			
			            ->where([['c.shop_id','in',$shop_id]])
			
			            //->where([['c.source_id','in',$xianshang]])
			
			            ->where([['cg.customer_setting_id_type','in',$bh]])
			
			            ->where(function($query) use ($where,$whereOr) {
			
			                    $query -> where(function($query) use ($where) {
			
			                        $query -> where($where);
			
			                    });
			
			                    $query -> whereOr(function($query) use ($whereOr) {
			
			                        $query -> where($whereOr);
			
			                    });
			
			                })
			
						
			            ->group('shop_id')
			
			            ->column('count(distinct c.id) as num,shop_id','shop_id');
						
						
			//疤痕成交新单
			$where = [];
			
			array_push($where,['c.first_time','>=',$start_time]);
			
			array_push($where,['c.first_time','<=',$end_time]);
			
			$whereOr = [];
			
			array_push($whereOr,['c.second_time','>=',$start_time]);
			
			array_push($whereOr,['c.second_time','<=',$end_time]);
			
			$scarBhCJ = Db::name('customer')->alias('c')
			
			            ->join('customer_scar cg', 'cg.customer_id=c.id', 'LEFT')
			
			            ->where([['c.shop_id','in',$shop_id]])
			
			            ->where([['c.cj_money','>=','500']])
			
			            ->where([['cg.customer_setting_id_type','in',$bh]])
			
			            ->where(function($query) use ($where,$whereOr) {
			
			                    $query -> where(function($query) use ($where) {
			
			                        $query -> where($where);
			
			                    });
			
			                    $query -> whereOr(function($query) use ($whereOr) {
			
			                        $query -> where($whereOr);
			
			                    });
			
			                })
			
						
			            ->group('shop_id')
			
			            ->column('count(distinct c.id) as num,shop_id','shop_id');
								
			
			
			/* ---------------------------------------疤痕 青春痘  项目 进店 统计结束----------------------------------------------------- */
			
			/* ---------------------------------------体验 疤痕 体验项目 体验痘痘  体验成交个数 统计开始----------------------------------------------------- */
			/* 体验--疤痕  代码是 168  体验痘痘 代码是 686  体验 项目代码是 150和165  */
			
			
			//疤痕档案
			$where = [];
			
			array_push($where,['c.create_time','>=',$start_time]);
			
			array_push($where,['c.create_time','<=',$end_time]);
			
			
			
			$scarBhDN = Db::name('customer')->alias('c')
			
			            ->join('customer_scar cg', 'cg.customer_id=c.id', 'LEFT')
			
			            ->where([['c.shop_id','in',$shop_id]])
			
			            //->where([['c.cj_money','>=','500']])
			
			            ->where([['cg.customer_setting_id_type','in',$bh]])
			
			            ->where($where) 
						
			            ->group('shop_id')
			
			            ->column('count(distinct c.id) as num,shop_id','shop_id');
			
			
			
			foreach ($arr as $key=>$value) {
				
				$arr[$key]['bhJdNum'] = isset($scarBhJD[$key]) ? $scarBhJD[$key]['num'] : 0;//疤痕进店
				
				$arr[$key]['bhCJNum'] = isset($scarBhCJ[$key]) ? $scarBhCJ[$key]['num'] : 0;//疤痕成交
				
				$arr[$key]['bhdan'] = isset($scarBhDN[$key]) ? $scarBhDN[$key]['num'] : 0;//疤痕档案
				
				
			}
			
			$arr=array_values($arr);
			
			// echo '<pre>';
			// print_r($arr);
			
		}
		
		
		
		
		
		
		
		//exit;
		
		
		
		
		
		
		
		
		// $shopList = Db::name('shop')->column('shop_name','shop_id');
		
		// $shop_personList = Db::name('shop_person')->column('name','id');

		
		// $where  = [];
		// //array_push($where,['member_label','like',649]);
		// array_push($where,['point_goods_id','=','28']);
		
		
		// //查询 所有标记 内拓成交 标签的客户档案信息  
		// $customerList= Db::name('customer')->alias('c')
		
		//             ->join('customer_point_goods_buy b', 'b.customer_id=c.id', 'LEFT')
		
		// 			->where($where)
					
		// 			->order('c.shop_id')
		
		// 			->column('c.id,c.shop_id,c.customer_name,c.telephone,b.use_time,b.person_id');
					
		// foreach ($customerList as $key =>$value)
		// {
			
		// 	$customerList[$key]['shop_name'] = $shopList[$value['shop_id']] ?? '';
			
		// 	$customerList[$key]['useTime']= $value['use_time'] > 0 ? date('Y-m-d',$value['use_time']) : '';
			
		// 	$customerList[$key]['personName']=  $shop_personList[$value['person_id']] ?? '';
		// }
		
		// // echo count($customerList);
		// // echo '<pre>';
		// // print_r($customerList);
		// // exit;
		
		$callName = [
		
		    'name'     =>'市场',
		
		    'shop_name'   =>'门店',
		
		    'bhdan'    =>'疤痕档案',
		
		    'bhJdNum'    =>'疤痕进店',
		
		    'bhCJNum'    =>'疤痕成交',
		
		
		];
		
		$timeStr = date('Y-m-d',time());
		
		$downUrl = exportExcelData('3月大促小程序预约明细',$callName,$arr,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
	}
	
	
	public function setFieveightcustomer(){
		
		echo '58进店数据';
		
		$type = $_GET['type'];
		//echo $type;exit;
		$start_time =strtotime($_GET['starttime']);
		$end_time=strtotime($_GET['endtime'])+86399;
		
		
		
		
		
		
		if($type == 1){  //查询网络二次进店且打标签档案
			
			$where  = [];
			$biaoqianCode = 659;
			array_push($where,['c.member_label','like','%'.$biaoqianCode.'%']);
			array_push($where,['c.source_id','in',[43,44,50,51,52,54,55,118,584,609,597]]);
			array_push($where,['c.second_time','between',"$start_time,$end_time"]);
			
			 
		}else if($type == 2){  //查询 线下疤痕进店数据
			
			$where  = [];
			//array_push($where,['c.member_label','=',659]);
			//array_push($where,['c.source_id','in',[654,49,48,47,46]]);
			array_push($where,['c.source_id','in',[43,44,50,51,52,54,55,118,584,609,597]]);
			//array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['c.create_time','>=',"$start_time"]);
			array_push($where,['c.create_time','<=',"$end_time"]);
			
		}else if($type == 3){ //查询黄V数据 约珍
			
			$where  = [];
			array_push($where,['c.source_id','=',597]);
			array_push($where,['c.create_time','>=',"$start_time"]);
			
			
		}else if($type == 4){ //查询打标签的客户档案
			
			$where  = [];
			$biaoqianCode = 659;
			array_push($where,['c.member_label','like','%'.$biaoqianCode.'%']);
			
		}else if($type == 5){ //查询打标签的网络客户档案
			
			$where  = [];
			$biaoqianCode = 660;
			array_push($where,['c.member_label','like','%'.$biaoqianCode.'%']);
			
		}else if($type == 6){
			$where  = [];
			
			$shoplist = [73782775492343,73782775074894,73782775019638];
			
			// array_push($where,['c.source_id','=',609]);
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			
			array_push($where,['c.create_time','>=',"$start_time"]);
			// array_push($where,['c.first_time','=',0]);
			array_push($where,['c.cj_money','=',0]);
			array_push($where,['c.shop_id','in',$shoplist]);
			
		}else if($type == 7){
			
			$where  = [];
			array_push($where,['c.cj_money','>=',500]);
			array_push($where,['c.cj_time','between',"$start_time,$end_time"]);
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
		}else if($type == 8){
			
			$where  = [];
			$biaoqianCode = 671;
			array_push($where,['c.member_label','like','%'.$biaoqianCode.'%']);
		}
		
		
		
		$customerList = Db::name('customer')->alias('c')
		
		->leftJoin('customer_scar cs', 'c.id=cs.customer_id')
		
		->leftJoin('customer_extra ce', 'c.id=ce.customer_id')
		
		->where($where)
		
		->column('c.id,c.shop_id,c.customer_name,c.telephone,c.source_id,c.first_time,c.second_time,c.member_label,c.operator,c.create_time,cs.customer_setting_id_type,ce.consultation_first_time,ce.consultation_second_time,ce.reach_first_time,ce.reach_second_time,c.cj_time,c.cj_money,c.remarks');
		//echo count($customerList);exit;
		// echo '<pre>';
		// print_r($customerList);exit;
		
		
		$shop_ids = array_values(array_unique(array_column($customerList,'shop_id')));
		
		
		$where = [];
		
		array_push($where,['shop_id','in',$shop_ids]);
		
		$shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');
	
		
		
		// 来源数组
		$where = [];
		
		array_push($where,['category_id','=',4]);
		
		$laiyuanList = Db::name('setting')->where($where)->column('name','id');
		
		// 标签数组
		$where = [];
		
		array_push($where,['category_id','=',7]);
		
		$bqList = Db::name('setting')->where($where)->column('name','id');
		
		
		// 客服数组
		$kefuList = Db::name('admin_user')->column('realname','id');
		
		//$personList     = Db::name('shop_person')->column('name', 'id');
		
		
		// 疤痕类型数组
		$where = [];
		
		array_push($where,['category_id','=',1]);
		
		$scarType = Db::name('setting')->where($where)->column('name','id');
		
		
		$list = [];
		
		foreach($customerList as $key => $val){
		
		
		    $one = [];
		
		    $one['customer_name'] = $val['customer_name'];
		
		    $one['telephone'] = $val['telephone'];
			
			
		    $one['shop_name'] = $shopList[$val['shop_id']] ?? '';
		
		    $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';
		
		    $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d',$val['create_time']) : '';
		
			$one['operatorN'] = $kefuList[$val['operator']] ?? '';
		
		    $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d ',$val['first_time']) : '';
		
		    $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d ',$val['second_time']) : '';
		
			$one['customer_setting_id_type'] = $val['customer_setting_id_type'];
			
			$one['scar_aggregate']   =$scarType[$val['customer_setting_id_type']] ?? '未知';
			
			$one['consultation_first_time'] = $val['consultation_first_time'] > 0 ? date('Y-m-d ',$val['consultation_first_time']) : '';
					
			$one['consultation_second_time'] = $val['consultation_second_time'] > 0 ? date('Y-m-d ',$val['consultation_second_time']) : '';
							
			$one['reach_second_time'] = $val['reach_second_time'] > 0 ? date('Y-m-d ',$val['reach_second_time']) : '';
							
			$one['reach_first_time'] = $val['reach_first_time'] > 0 ? date('Y-m-d ',$val['reach_first_time']) : '';
			
			$one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d ',$val['cj_time']) : '';
			
			$one['cj_money'] = $val['cj_money'];
			$one['remarks'] = $val['remarks'];
					
			$one['member_label'] = $bqList[$val['member_label']] ?? '';
			
		    array_push($list,$one);
		}
		
		// echo '<pre>';
		// print_r($list);exit;
		
		
		
		$callName = [
		
			'create_time'    =>'录入时间',
			
		    'customer_name'   =>'姓名',
		
		    'shop_name' =>'门店',
		
		    
		
			//'sex' =>'性别',
		
		    'telephone'     =>'手机号',
		
		    'source_name'   =>'来源',
		
			'member_label'   =>'标签',
		
			'operatorN' =>'客服',
			
			'customer_setting_id_type' =>'疤痕类型1',
			
			'scar_aggregate' =>'疤痕类型',
			
		    'first_time'    =>'首次进店',
		
		    'second_time'    =>'二次进店',
			
			'consultation_first_time'    =>'首次看诊',
			
			'consultation_second_time'    =>'二次看诊',
			
			'reach_first_time'    =>'首次到院',
			
			'reach_second_time'    =>'二次到院',
			
			'cj_time'    =>'成交时间',
			
			'cj_money'    =>'成交金额',
			
			'remarks'    =>'备注',
		
		
		
		];
		
		$downUrl = exportExcelData('5月标签客户档案明细',$callName,$list,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
	}
	
	
	public function setScarCustomerListY(){ 
		
		echo '数据进店';
		
		$clientList = Db::name('customer_balance')
		
					->where('shop_id','in',$shopIds)
		
					//->where('money','<>',0)
		
					//->whereOr('complimentary_money','<>',0)
		
					->column('customer_id,balance_category_id,money,complimentary_money,shop_id');
		
		
		
		
	}
	
	
	
	
	
	
	public function setCustomerInfoPingci(){ 
		
		echo '客户到店频次数据';
		
		// $start_time =strtotime($_GET['starttime']);
		// $end_time=strtotime($_GET['endtime'])+86399;
		$start_time = '1704038400';
		$end_time = '1718035199';
		// $where  = [];
		// array_push($where,['s.status','=',1]);  //门店正常运营未关闭
		// array_push($where,['s.shop_type','=',2]);//只查询门店
		// array_push($where,['s.type','<>',2]);//不查询加盟门店
		// array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);  //进店时间在该区间
		
		
		// $customerList = Db::name('customer')->alias('c')
		
		// ->leftJoin('shop s', 'c.shop_id=s.shop_id')
		
		// ->where($where)
		
		// ->column('c.id,c.shop_id,c.customer_name,c.telephone,c.source_id,c.first_time,c.second_time,s.shop_name');
		
		
		// echo count($customerList);//exit;
		// echo '<pre>';
		// print_r($customerList);exit;
		
		
		// foreach($customerList as $key => $val){
			
		// 	//查询该客户
			
		// 	//$servicesList = 
			
			
			
		// }
		
		
		$where = [];
		//$shopIDS = [73782775246614,73782775223895,73782775249465,73782775342427,73782776799504];
		$shopIDS = [73782776799504];
		array_push($where,['type','<>',2]);
		array_push($where,['status','=',1]);
		array_push($where,['shop_type','=',2]);
		array_push($where,['shop_id','in',$shopIDS]);
		$shopList = Db::name('shop')->where($where )->column('shop_name,shop_id');
		
		
		
		
		foreach($shopList as $key => $value){
			
			
			$clientList= Db::name('customer')->alias('c')
			
			            ->join('shop_refund_order s', 'c.id=s.uid', 'LEFT')
						
						//->where('c.cj_money','>=',500)
						
						->where('c.member_classification','>=',70)
						
						->where('c.shop_id','=',$value['shop_id'])
						
						->where('s.deal_time','>=',$start_time)
						//->where('deal_time','<=',$end_time)
						
						->where('s.type','=',3)
						->where('s.status','=',2)
						->where('s.refund_type','<>',101.03)
						
						//->having('sum(pay_amount * coefficient) >=500 and sum(pay_amount * coefficient) < 5000')
						->having('sum(pay_amount) >= 50000')
						->group('s.uid')
			
						->column('uid,sum(pay_amount) as num,telephone,c.shop_id');
			
			
			echo '<pre>';
			print_r($clientList);
			$fiveNum = 0;
			$threeNum = 0;
			$twoNum = 0;
			$oneNum = 0;
			
			foreach($clientList as $k => $v){
				
				$customerlistTK =Db::name('shop_refund_order')->alias('s')
				
					->join('shop_refund_order_goods o', 's.id=o.order_id', 'LEFT')
				
					->where('s.deal_time','>=',$start_time)
					->where('s.deal_time','<=',$end_time)
					->where('s.uid','=',$v['uid'])
					->where('s.type','=',1)
					->where('s.status','=',2)
					->where('o.refund_mode','<>','06')
					->where('s.refund_type','<>',101.03)
					->column('sum(s.pay_amount) as count,s.uid');
				
				
				
				$customerAmount = bcsub($v['num'],$customerlistTK[0]['count'],2);
				
				
				
				if($customerAmount>= 50000){
					
					$fiveNum ++;
					
				}
				
				// if($customerAmount>= 15000 and $customerAmount < 50000){
					
				// 	$threeNum ++;
					
				// }
				
				// if($customerAmount>= 5000 and $customerAmount < 15000){
					
				// 	$twoNum ++;
					
				// }
				
				// if($customerAmount>=500 and $customerAmount < 5000){
					
				// 	$oneNum ++;
					
				// }
				
				
			}
			
			
			// echo $fiveNum;
			
			// $clientList = Db::name('customer')
			
			// 			->where('shop_id','=',$value['shop_id'])
			
			// 			->where('cj_money','>=',500)
			
			// 			->column('id,customer_name,telephone');
			
			
			// foreach($clientList as $k => $v){
				
			// 	$customerlistSK =Db::name('shop_refund_order')
			// 	    ->where('deal_time','>=',$start_time)
			// 	    ->where('deal_time','<=',$end_time)
			// 	    ->where('uid','=',$v['id'])
			// 		->where('type','=',3)
			// 	    ->where('status','=',2)
			// 		->where('refund_type','<>',101.03)
			// 	    ->column('sum(pay_amount) as count,uid');
				
				
			// 	$customerlistTK =Db::name('shop_refund_order')
			// 	    ->where('deal_time','>=',$start_time)
			// 	    ->where('deal_time','<=',$end_time)
			// 	    ->where('uid','=',$v['id'])
			// 		->where('type','=',1)
			// 	    ->where('status','=',2)
			// 		->where('refund_type','<>',101.03)
			// 	    ->column('sum(pay_amount) as count,uid');
				
			// 	//收款单金额合计  减去 退款单金额合计 
				
			// 	$customerAmount = bcsub($customerlistSK[0]['count'],$customerlistTK[0]['count'],2);
				
				/* 年度累计消费金额大于等于 50000 */
				// if($customerAmount>= 50000){
					
				// 	$fiveNum ++;
					
				// }
				// /* 年度累计消费金额大于等于 15000 小于 50000 */
				// if($customerAmount >= 15000 && $customerAmount < 50000){
					
				// 	$threeNum ++;
					
				// }
				
				// /* 年度累计消费金额大于等于 5000 小于等于 15000 */
				// if($customerAmount >= 5000 && $customerAmount < 15000){
					
				// 	$twoNum ++;
					
				// }
				
				// /* 年度累计消费金额大于 0 小 5000 */
				// if($customerAmount >0 && $customerAmount < 5000){
					
				// 	$oneNum ++;
					
				// }
				
			//}
			
			
			
			
			
			//$shopList[$key]['cjNum'] = count($clientList);
			
			$shopList[$key]['num5'] = $fiveNum;
			//$shopList[$key]['num2'] = $threeNum;
			//$shopList[$key]['num1'] = $twoNum;
			//$shopList[$key]['num'] = $oneNum;
			
		}
		
		
		echo '<pre>';
		print_r($shopList);exit;
		
		$callName = [
		
			'shop_name'    =>'门店',
			
		    //'num5'   =>'年度消费金额5w以上',
			
			//'num2'   =>'年度消费金额1.5w以上',
			
			//'num1'   =>'年度消费金额0.5w以上',
			
			'num'   =>'年度消费金额0.5w以下',
		
			
		];
		
		$downUrl = exportExcelData('客户价值（2024）',$callName,$shopList,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
		
		
		
		
	}
	
	/* 中台系统 ABC类客户维护 */
	public function setCustomerAlist(){
		echo '数据-A客户新增';
		$start_time = '1704038400';
		$where = [];
		
		//$shopIDS = [73782775246614,73782775223895,73782775249465,73782775342427,73782776799504];
		
		array_push($where,['member_classification','>=',70]);
		array_push($where,['cj_time','=',0]);
		array_push($where,['cj_money','=',0]);
		//array_push($where,['shop_id','in',$shopIDS]);
		//array_push($where,['shop_id','=',73782775223895]);
		//array_push($where,['telephone','=',13698612489]);
		
		$customer_list = Db::name('customer')->where($where)
		// ->count();
		// ->limit(10000,5000)
		->column('id,customer_name,telephone,member_classification,cj_money,cj_time,consumption');
		
		
		//echo count($customer_list);exit;
		
		
		
		// foreach($customer_list as $k => $v){
			
		// 	//门店查询
		// 	$clientamount1 = Db::name("shop_refund_order")
			
		// 			->where('uid','=',$v['id'])
			
		// 			//->where('deal_time','>=',$cjstarttime)
			
		// 			//->where('deal_time','<=',$cjendtimett)
		// 			//->where('order_type','=',1)
					
		// 			//->where('pay_amount','>=',500)
		// 			->where('status','=',2)
		// 			->where('type','=',3)
			
		// 			->where('refund_type','<>',101.03)
			
		// 			->where('deal_time','>=',$start_time)
					
		// 			//->having('sum(pay_amount * coefficient) >= 50000')
					
		// 			->group('uid')
		// 			->column('uid,sum(pay_amount) as num');
			
		// 	if(empty($clientamount1)){
		// 		$customerAmount = 0;
		// 	}else{
				
			
		// 		$skAmount = 0;
		// 		$tkAmount = 0;
				
		// 		foreach($clientamount1 as $kk => $val){
					
		// 			$customerlistTK =Db::name('shop_refund_order')->alias('s')
					
		// 				->join('shop_refund_order_goods o', 's.id=o.order_id', 'LEFT')
					
		// 				->where('s.deal_time','>=',$start_time)
		// 				//->where('s.deal_time','<=',$end_time)
		// 				->where('s.uid','=',$val['uid'])
		// 				->where('s.type','=',1)
		// 				->where('s.status','=',2)
		// 				->where('o.refund_mode','<>','06')
		// 				->where('s.refund_type','<>',101.03)
		// 				->column('sum(s.pay_amount) as count,s.uid');
					
					
		// 			$skAmount+= $val['num'];
					
		// 			$tkAmount+= $customerlistTK[0]['count'];
					
					
					
		// 		}
		// 		$customerAmount = bcsub($skAmount,$tkAmount,2);
		// 	}
		// 	// echo '收款金额合计--'.$skAmount;echo '<br/>';
		// 	// echo '退款金额合计--'.$tkAmount;echo '<br/>';
			
			
			
		// 	$customer_list[$k]['num'] =$customerAmount;
			
		// }
		// echo '<pre>';
		// print_r($customer_list);
		// exit;
		
		$callName = [
		
			'customer_name'    =>'客户姓名',
			
		    'telephone'   =>'手机号',
			
			'member_classification'   =>'客户分类',
			
			'cj_money'   =>'成交金额',
			
			'consumption'   =>'累计金额',
			
			//'num'   =>'年度消费金额',
		
			
		];
		
		$downUrl = exportExcelData('大观店',$callName,$customer_list,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
		
		
		
		
	}
	
	
	
	// public function setnumlist_y($startime,$clientId){
		
		
		
	// }
	
	
	
	//刷客户数据的代码
	public function swipeCustomerData(){
		
		$where = [];
		$whereType = $baseWhere = $whereOr =  [];
		$start_time =  strtotime("2023-07-01");
		$end_time = strtotime("2024-06-30 23:59:59");
		$field = 's.shop_id, s.shop_name,s.market_id,s.shop_type,m.name';
		array_push($where, ['s.status', '=', 1]);//运营中的数据
		array_push($where, ['s.shop_id', '<>', 74076159037951]);//只有直营店数据
//        array_push($where,['s.shop_id','=',73782774991693]); //重庆门诊
		//array_push($where,['s.shop_id','=',79275573413324]);//瀚宇天悦店
		//array_push($where,['s.shop_id','=',73782774742277]);//平阳路

		$list = Db::name('shop')->alias('s')
			->join('shop_market m', 's.market_id = m.id', 'LEFT')
			->where($where)
			->field($field)
			->select()->toArray();

		if ($list) {
			$arr = array_column($list,null,'shop_id');

			$bh = [1, 2, 3, 4, 5, 6, 7, 8,655,656];
			$dd = [10];
			$xm = [9,11, 12];
			array_push($whereOr, ['a.first_time', 'between', [$start_time, $end_time]]);
			array_push($whereOr, ['a.second_time', 'between', [$start_time, $end_time]]);
			$whereOne = array_merge($baseWhere, [['b.customer_setting_id_type', 'in', $xm]]);
			//皮肤客户进店数
			$pf_enter_shop = Db::name('customer')->alias('a')
				->join('customer_scar b', 'a.id = b.customer_id', 'left')
				->join('shop sh', 'a.shop_id = sh.shop_id', 'left')
				->field('count(distinct a.id) as num,a.shop_id')
				->where($whereType)
				->where($whereOne)
				->where(function ($query) use ($whereOr) {
					$query->whereOr($whereOr);
				})
				->group('a.shop_id')
				->select()->toArray();
//            var_dump($pf_enter_shop);die;

			$pf_enter_shop = array_column($pf_enter_shop,null,'shop_id');

			//查询当前时间段的 成交个数 begin
			$maps = [
				['cj_time', 'between', "$start_time,$end_time"],
			];
			
			// echo '<pre>';
			// print_r($arr);exit;
			//查询当前时间段的 成交个数 end
			foreach ($arr as $key=>&$value) {
				//获取皮肤进店的id
				$customer_id = Db::name('customer')->alias('a')
					->join('customer_scar b', 'a.id = b.customer_id', 'left')
					->join('shop sh', 'a.shop_id = sh.shop_id', 'left')
					->field('count(distinct a.id) as num,a.shop_id')
					->where($whereType)
					->where($whereOne)
					->where(function ($query) use ($whereOr) {
						$query->whereOr($whereOr);
					})
					->where('a.shop_id',$value['shop_id'])->column('a.id');
//                var_dump($customer_id);die;
				if(!empty($customer_id)){
					//成交数
					$receive_deal=Db::name('customer')->where($maps)
						->field('count(DISTINCT(id)) as sum,shop_id')
						->where('shop_id',$value['shop_id'])
						->whereIn('id',$customer_id)
						->count();
//                        ->column('id');
                   //var_dump($receive_deal);die;
					// //进店数据
					// $value['enter_shop'] = isset($pf_enter_shop[$key])?$pf_enter_shop[$key]['num'] :0; //客户进店数
					// //成交数
					// $value['receive_deal'] = $receive_deal;
					// //成交率
					// $value['turnover_rate'] = $value['enter_shop'] >0 ? (bcdiv( $value['receive_deal'],$value['enter_shop'],4)*100).'%' :'0%';
					
					
					// // if(in_array($value['market_id'],[8,9,10,11]))
					// // {
					// // 	$newsXDMoney = $this->sumShopPayMoneyNewMZ('XD',$value['shop_id'],$value['shop_type'],$start_time,$end_time,'',$customer_id);
					// // 	$budanMoney = $this->sumShopPayMoneyNewMZ('BD',$value['shop_id'],$value['shop_type'],$start_time,$end_time,'',$customer_id);
					// // }else{
					// // 	$newsXDMoney = $this->sumShopPayMoneyNew('XD',$value['shop_id'],$value['shop_type'],$start_time,$end_time,'',$customer_id);
					// // 	$budanMoney = $this->sumShopPayMoneyNew('BD',$value['shop_id'],$value['shop_type'],$start_time,$end_time,'',$customer_id);
					
					// // }
					// //新单额
					// $value['new_total'] = 111;//新单金额
					// //补单金额
					// $value['bd_total'] = 222;//补单金额
					// //整体消费额度
					// $value['sum'] = 333;
					
					
					// /* 查询 疤痕成交客户 转化数据 */
					
					// $scarList = Db::name('customer')
					//     ->alias('b')
					//     ->where('b.cj_time','>=',$start_time)//2022.01.01
					//     ->where('b.cj_time','<=',$end_time) //2022.01.31
					//     ->where('b.cj_money','>',0)
					// 	->whereIn('b.id',$customer_id)
					//     ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
					//     ->where('sp.business_type','=',1)
					//     ->where('sp.type','=',3)
					// 	->where('sp.shop_id',$value['shop_id'])
					//     ->column('b.id,b.shop_id,b.cj_money,telephone');
					
					
					// // echo '<pre>';
					// // print_r($scarList);
					// $bahenNum = 0;
					// $xiangmuNum = 0;
					// foreach ($scarList as $k1 => $val) {
						
					// 	$bahentwoSK =Db::name('shop_refund_order')
					// 	    ->where('deal_time','>=',$start_time)
					// 	    ->where('deal_time','<=',$end_time)
					// 	    ->where('uid','=',$val['id'])
					// 		//->where('uid','in',$customerIds)
					// 		->where('type','=',3)
					// 	    ->where('status','=',2)
					// 		->where('business_type','=',1)
					// 		->where('refund_type','<>',101.03)
					// 	    ->column('sum(pay_amount) as count,shop_id');
					// 	//疤痕成交退款单合计
					// 	$bahentwoTK =Db::name('shop_refund_order')
					// 	    ->where('deal_time','>=',$start_time)
					// 	    ->where('deal_time','<=',$end_time)
					// 	    ->where('uid','=',$val['id'])
					// 		//->where('uid','in',$customerIds)
					// 		->where('type','=',1)
					// 	    ->where('status','=',2)
					// 		->where('business_type','=',1)
					// 		->where('refund_type','<>',101.03)
					// 	    ->column('sum(pay_amount) as count,shop_id');
					// 	$totalamountwo = bcsub($bahentwoSK[0]['count'], $bahentwoTK[0]['count'],2);
					// 	//$totalamount = bcsub($totalamountwo, $val['cj_money'],2);
						
					// 	$bahenNum = $totalamountwo+$bahenNum;
						
						
					// 	//项目普及率的计算公式
					// 	$xiangmtwoSK =Db::name('shop_refund_order')
					// 		->where('deal_time','>=',$start_time)
					// 		->where('deal_time','<=',$end_time)
					// 		->where('uid','=',$val['id'])
					// 		//->where('uid','in',$customerIds)
					// 		->where('type','=',3)
					// 	    ->where('status','=',2)
					// 		->where('business_type','=',2)
					// 		->where('refund_type','<>',101.03)
					// 		->column('sum(pay_amount) as count,consignee,shop_id');
						
					// 	$xiangmtwoTK =Db::name('shop_refund_order')
					// 		->where('deal_time','>=',$start_time)
					// 		->where('deal_time','<=',$end_time)
					// 		->where('uid','=',$val['id'])
					// 		//->where('uid','in',$customerIds)
					// 		->where('type','=',1)
					// 	    ->where('status','=',2)
					// 		->where('business_type','=',2)
					// 		->where('refund_type','<>',101.03)
					// 		->column('sum(pay_amount) as count,shop_id');
					// 	$totalamountXM = bcsub($xiangmtwoSK[0]['count'], $xiangmtwoTK[0]['count'],2);
						
					// 	$xiangmuNum = $totalamountXM+$xiangmuNum;
					// }
					// //echo '疤痕总金额---'.$bahenNum;
					
					// $value['new_bahNum'] = $bahenNum;//新单金额
					
					// $value['new_xaingmuNum'] = $xiangmuNum;//新单金额
					
					
					/* 查询 项目成交客户 转化数据 */
					
					$scarListXM = Db::name('customer')
					    ->alias('b')
					    ->where('b.cj_time','>=',$start_time)//2022.01.01
					    ->where('b.cj_time','<=',$end_time) //2022.01.31
					    ->where('b.cj_money','>',0)
						->whereIn('b.id',$customer_id)
					    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
					    ->where('sp.business_type','=',2)
					    ->where('sp.type','=',3)
						->where('sp.shop_id',$value['shop_id'])
					    ->column('b.id,b.shop_id,b.cj_money,telephone');
					
					
					// echo '<pre>';
					// print_r($scarList);
					$bahenXMNum = 0;
					$xiangmuXMNum = 0;
					
					$xmNum = 0;
					foreach ($scarListXM as $k1 => $val) {
						
						// $bahentwoSKT =Db::name('shop_refund_order')
						//     ->where('deal_time','>=',$start_time)
						//     ->where('deal_time','<=',$end_time)
						//     ->where('uid','=',$val['id'])
						// 	//->where('uid','in',$customerIds)
						// 	->where('type','=',3)
						//     ->where('status','=',2)
						// 	->where('business_type','=',1)
						// 	->where('refund_type','<>',101.03)
						//     ->column('sum(pay_amount) as count,shop_id');
						// //疤痕成交退款单合计
						// $bahentwoTKT =Db::name('shop_refund_order')
						//     ->where('deal_time','>=',$start_time)
						//     ->where('deal_time','<=',$end_time)
						//     ->where('uid','=',$val['id'])
						// 	//->where('uid','in',$customerIds)
						// 	->where('type','=',1)
						//     ->where('status','=',2)
						// 	->where('business_type','=',1)
						// 	->where('refund_type','<>',101.03)
						//     ->column('sum(pay_amount) as count,shop_id');
						// $totalamountwo = bcsub($bahentwoSKT[0]['count'], $bahentwoTKT[0]['count'],2);
						// //$totalamount = bcsub($totalamountwo, $val['cj_money'],2);
						
						// $bahenXMNum = $totalamountwo+$bahenXMNum;
						
						
						//项目普及率的计算公式
						$xiangmtwoSKT =Db::name('shop_refund_order')
							->where('deal_time','>=',$start_time)
							->where('deal_time','<=',$end_time)
							->where('uid','=',$val['id'])
							//->where('uid','in',$customerIds)
							->where('type','=',3)
						    ->where('status','=',2)
							->where('business_type','=',2)
							->where('refund_type','<>',101.03)
							->column('sum(pay_amount) as count,consignee,shop_id');
						
						$xiangmtwoTKT =Db::name('shop_refund_order')
							->where('deal_time','>=',$start_time)
							->where('deal_time','<=',$end_time)
							->where('uid','=',$val['id'])
							//->where('uid','in',$customerIds)
							->where('type','=',1)
						    ->where('status','=',2)
							->where('business_type','=',2)
							->where('refund_type','<>',101.03)
							->column('sum(pay_amount) as count,shop_id');
						$totalamountXM = bcsub($xiangmtwoSKT[0]['count'], $xiangmtwoTKT[0]['count'],2);
						
						if($totalamountXM >= '500'){
							$xmNum++;
							
							//echo '客户数据---';echo '<br/>';
						}
						
						//$xiangmuXMNum = $totalamountXM+$xiangmuXMNum;
					}
					//echo '疤痕总金额---'.$bahenNum;
					
					//$value['new_bahNumT'] = $bahenXMNum;//新单金额
					
					//$value['new_xaingmuNumT'] = $xiangmuXMNum;//新单金额
					
					
				}
				$arr[$key]['xmNum'] = $xmNum;//新单金额
			}
		}
		
		// echo '<pre>';
		// print_r($arr);exit;
		
		
		
		$callName = [
		
		
		    'name' =>   '市场名称',
		
		    'shop_name' =>'门店名称',
		
		//     'enter_shop' =>'疤痕进店数',
		
		//     'receive_deal' =>'成交数',
		
		//     'turnover_rate' =>'成交率',
		
		//     'new_total'     =>'新单额',
		
		//     'sum'   =>'整体消费额',
			
		// 	'new_bahNum'   =>'疤痕转疤痕业绩',
			
		// 	'new_xaingmuNum'   =>'疤痕转项目业绩',
			
		// 	'new_bahNumT'   =>'项目转化疤痕业绩',
			
		// 	'new_xaingmuNumT'   =>'项目转项目业绩',
		
		//     'zongti_sum' =>'总体金额',
		
		    //'rate' => '占总体比例'
			
			'xmNum' => '项目转化'
			
			
			
		];
		
		$downUrl = exportExcelData('项目年度数据分析',$callName,$arr,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
		
		
		
		
	}
	
	
	public function setCustomerInfoList(){
		
		echo '数据导出-2024';
		
		$start_time =strtotime($_GET['starttime']);
		$end_time=strtotime($_GET['endtime'])+86399;
		
		// echo $starttime;echo '<br/>';
		// echo $endtime;echo '<br/>';
		
		// [43,44,50,51,52,54,55,118,584,609]
		/* 查询年度不同类型客户的进店数据 */
		
		$where = [];
		array_push($where,['type','<>',2]);
		array_push($where,['status','=',1]);
		array_push($where,['shop_type','=',2]);
		array_push($where,['shop_id','<>',74076159037951]);
		//array_push($where,['shop_id','in',[78808830816801,73782774859952,73782774813357]]);
		$shopList = Db::name('shop')->where($where )->column('shop_name,shop_id');
		
		
		$bhList = [1,2,3,4,5,6,7,8,655,656];
		$xmList = [9,11,12];
		$ddList = [10];
		
		
		foreach ($shopList as $key=>&$value) {
			
			/* 查询门头外联档案数据 和进店数据 及成交数据 */
			//echo '门店id---'.$value['shop_id'].'<br/>';
			//门头外联进店
			$where = [];
			array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
			//array_push($where,['source_id','in', [43,44,50,51,52,54,55,118,584,609]]);
			//array_push($where,['source_id','=', 49]);
			array_push($where,['shop_id','=', $value['shop_id']]);
			$customerMWList= Db::name('customer')->where($where)->column('id');
			
			
			//门头外联 疤痕进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', $bhList ]);
			array_push($where,['c.source_id','in', [43,44,50,51,52,54,55,118,584,609]]);
			//array_push($where,['c.source_id','=', 49]);
			array_push($where,['c.shop_id','=', $value['shop_id']]);
			$customerMBHList= Db::name('customer')->alias('c')
			
			            ->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
						->where($where)
			
						->column('c.id,c.customer_name,c.shop_id');
						
			//门头外联 皮肤进店 统计
			$where = [];
			array_push($where,['c.first_time|c.second_time','between',"$start_time,$end_time"]);
			array_push($where,['s.customer_setting_id_type','in', $xmList ]);
			array_push($where,['c.source_id','in', [43,44,50,51,52,54,55,118,584,609]]);
			//array_push($where,['c.source_id','=', 49]);
			array_push($where,['c.shop_id','=', $value['shop_id']]);
			$customerMPFList= Db::name('customer')->alias('c')
			
						->join('customer_scar s', 'c.id=s.customer_id', 'LEFT')
			
						->where($where)
			
						->column('c.id,c.customer_name,c.shop_id');
			
			
			
			//门头外联成交个数
			$where = [];
			array_push($where,['cj_time','>=',$start_time]);
			array_push($where,['cj_time','<=',$end_time]);
			array_push($where,['cj_money','>',0]);
			array_push($where,['source_id','in', [43,44,50,51,52,54,55,118,584,609]]);
			//array_push($where,['source_id','=', 49]);
			array_push($where,['shop_id','=', $value['shop_id']]);
			$customerMWCJList= Db::name('customer')->where($where)->column('id');
			//echo $customerMWCJList= Db::name('customer')->getlastsql();
			//门头外联成交新单金额
			$where = [];
			array_push($where,['cj_time','>=',$start_time]);
			array_push($where,['cj_time','<=',$end_time]);
			array_push($where,['cj_money','>',0]);
			array_push($where,['source_id','in', [43,44,50,51,52,54,55,118,584,609]]);
			//array_push($where,['source_id','=', 49]);
			array_push($where,['shop_id','=', $value['shop_id']]);
			$customerMWCJAmount= Db::name('customer')->where($where)->column('sum(cj_money) as amount,shop_id');
			// // echo '<pre>';
			// print_r($customerMWCJAmount);
			
			//echo '<br/>';
			//门头外联成交疤痕个数
			
			$scarMLBHList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time)//2022.01.01
			    ->where('b.cj_time','<=',$end_time) //2022.01.31
			    ->where('b.cj_money','>',0)
				->whereIn('b.source_id',[43,44,50,51,52,54,55,118,584,609])
				//->where('b.source_id','=',49)
				
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',1)
			    ->where('sp.type','=',3)
				->where('sp.shop_id',$value['shop_id'])
			    ->column('b.id,b.shop_id,b.cj_money,telephone');
				//echo $scarMLBHList= Db::name('customer')->getlastsql();die;
			$scarMLxmList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time)//2022.01.01
			    ->where('b.cj_time','<=',$end_time) //2022.01.31
			    ->where('b.cj_money','>',0)
				->whereIn('b.source_id',[43,44,50,51,52,54,55,118,584,609])
				//->where('b.source_id','=',49)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',2)
			    ->where('sp.type','=',3)
				->where('sp.shop_id',$value['shop_id'])
			    ->column('b.id,b.shop_id,b.cj_money,telephone');
				
			$scarMLddList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time)//2022.01.01
			    ->where('b.cj_time','<=',$end_time) //2022.01.31
			    ->where('b.cj_money','>',0)
				->whereIn('b.source_id',[43,44,50,51,52,54,55,118,584,609])
				//->where('b.source_id','=',49)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',3)
			    ->where('sp.type','=',3)
				->where('sp.shop_id',$value['shop_id'])
			    ->column('b.id,b.shop_id,b.cj_money,telephone');
				
			$scarMLhfpList = Db::name('customer')
			    ->alias('b')
			    ->where('b.cj_time','>=',$start_time)//2022.01.01
			    ->where('b.cj_time','<=',$end_time) //2022.01.31
			    ->where('b.cj_money','>',0)
				->whereIn('b.source_id',[43,44,50,51,52,54,55,118,584,609])
				//->where('b.source_id','=',49)
			    ->leftjoin('shop_refund_order sp','b.cj_order_id = sp.id')
			    ->where('sp.business_type','=',4)
			    ->where('sp.type','=',3)
				->where('sp.shop_id',$value['shop_id'])
			    ->column('b.id,b.shop_id,b.cj_money,telephone');
			
			
			
			// $scarMoneyAllOne =$this->sumShopPayMoneyNewAllList('Bh',$customerMWList,2,$start_time,$end_time);//门头外联 疤痕业绩
			
			// $projectMoneyAllOne =$this->sumShopPayMoneyNewAllList('XM',$customerMWList,2,$start_time,$end_time);//门头外联 疤痕业绩
			
			$newsXDMoneyAllTwo =$this->sumShopPayMoneyNewAllList('XD',$customerMWList,2,$start_time,$end_time);//门头外联 新单业绩
			$budanMoneyAllOne =$this->sumShopPayMoneyNewAllList('BD',$customerMWList,2,$start_time,$end_time);//门头外联 新单业绩
			
			
			
			
			
			$shopList[$key]['mwJDnum'] = count($customerMWList);//门头外联进店
			$shopList[$key]['mbhJDnum'] = count($customerMBHList);//门头外联疤痕进店
			$shopList[$key]['mxmJDnum'] = count($customerMPFList);//门头外联项目进店
			
			$shopList[$key]['mCJnum'] = count($customerMWCJList);//门头外联成交个数
			$shopList[$key]['mBHCJnum'] = count($scarMLBHList);//门头外联疤痕成交个数
			$shopList[$key]['mXMCJnum'] = count($scarMLxmList);//门头外联项目成交个数
			$shopList[$key]['mDDCJnum'] = count($scarMLddList);//门头外联青春痘成交个数
			$shopList[$key]['mHFPCJnum'] = count($scarMLhfpList);//门头外联护肤品成交个数
			
			// /*  新单业绩 总业绩  成交疤痕业绩 成交项目业绩 */
			
			
			
			
			// $shopList[$key]['mXMCJamount'] = $customerMWCJAmount[0]['amount'];//门头外联项目新单金额合计
			
			// $shopList[$key]['mBHamount'] =$scarMoneyAllOne[$value['shop_id']]??0;// 门头外联疤痕业绩
			// $shopList[$key]['mXMamount'] =$projectMoneyAllOne[$value['shop_id']]??0;// 门头外联项目业绩
			$newXDAmount = $newsXDMoneyAllTwo[$value['shop_id']]??0;
			$newBDAmount = $budanMoneyAllOne[$value['shop_id']]??0;
			
			$shopList[$key]['xbdAmount'] = bcadd($newXDAmount,$newBDAmount,2);// 门头外联新单业绩
			// $shopList[$key]['bdAmount'] = $budanMoneyAllOne[$value['shop_id']];// 门头外联补单业绩
			
			
			// $shopList[$key]['zjsJDnum'] = count($customerZJSList);//转介绍进店
			// $shopList[$key]['zbhJDnum'] = count($customerZBHList);//转介绍疤痕进店
			// $shopList[$key]['zxmJDnum'] = count($customerZPFList);//转介绍项目进店
			
			// $shopList[$key]['zCJnum'] = count($customerZJSCJList);//转介绍成交个数
			
			// $shopList[$key]['ZBHCJnum'] = count($scarZBHList);//门头外联项目成交个数
			// $shopList[$key]['ZXMCJnum'] = count($scarZxmList);//门头外联项目成交个数
			
		}
		
		
		// echo '<pre>';
		// print_r($shopList);exit;
		
		$callName = [
			
		    'shop_name' =>'门店名称',
		
		//     'mwJDnum' =>'总进店',
		
		//     'mbhJDnum' =>'疤痕进店',
		
		//     'mxmJDnum' =>'项目进店',
		
		//      'mCJnum'     =>'成交个数',
		
		//     'mBHCJnum'   =>'疤痕成交个数',
			
		// 	'mXMCJnum'   =>'项目成交个数',
			
		// 	'mDDCJnum'   =>'青春痘成交个数',
			
		// 	'mHFPCJnum'   =>'护肤品成交个数',
			
		// 	'mXMCJamount'   =>'成交新单金额',
			
		// 	'mBHamount'   =>'疤痕业绩',
			
		// 	'mXMamount'   =>'项目业绩',
		
		    'xbdAmount' =>'转介绍总业绩',
			
			
			
		];
		
		$downUrl = exportExcelData('网络数据分析',$callName,$shopList,'xlsx',1);
		
		echo "<script language='javascript'>";
		
		echo " location='".$downUrl."';";
		
		echo "</script>";
		
		
		
		
		
	}
	
	
	/* 业务类型，客户ID  门店/门诊 开始时间 结束数据 */
	public function sumShopPayMoneyNewAllList($scene,$ids,$shop_type,$startTime,$endTime){
		//$where =['shop_id'=> $shopId,'type'=>3];
		switch ($scene) {
			case 'Bh':
	
		        $where['business_type']=1;
				$wheret['r.business_type']=1;
				break;
	
		    case 'XM':
	
		        $where['business_type']=2;
				$wheret['r.business_type']=2;
				break;
	
			case 'XD':
				
				$where['order_type']=1;
				$wheret['r.order_type']=1;
				break;
	
			case 'BD':
	
				$where['order_type']=2;
				$wheret['r.order_type']=2;
				break;
	
		    default:
	
		        break;
	
		}
		//收款单中的疤痕业绩 不含店转的业绩--01
		$receive = Db::name('shop_refund_order')->alias('r')
	
	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')
	
	        //->field('sum(g.pay_price * r.coefficient) as pay_amount')
			->field('sum(g.pay_price) as pay_amount,r.shop_id')
	
	        ->where($wheret)
			
			->where('r.uid','in',$ids)
			
	        ->where('g.refund_mode','<>','06')
	
	        ->where('r.type','=',3)
	
	        ->where('r.status','=',2)
	
	        ->where('r.refund_type','=','101.02')
	
	        ->when($startTime ?? 0, function ($query) use ($startTime) {
	
	            return $query->whereTime('deal_time', '>=', $startTime);
	
	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {
	
	            return $query->whereTime('deal_time', '<=', $endTime);
	
	        },function ($query) use ($endTime) {
	
	            return $query->whereMonth('deal_time');
	
	        })
	
	        ->group('r.shop_id')->select()->toArray();
	
	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');
	
	
		//退款单中的疤痕业绩	--不含店转的业绩  --02
		$refund = Db::name('shop_refund_order')->alias('r')
	
	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')
	
	        //->field('sum(g.pay_price * r.coefficient) as pay_amount')
			->field('sum(g.pay_price) as pay_amount,r.shop_id')
	
	        ->where($wheret)
	
			->where('r.uid','in',$ids)
	
	        ->where('g.refund_mode','<>','06')
	
	        ->where('r.type','=',1)
	
	        ->where('r.status','=',2)
	
	        ->where('r.refund_type','=','101.02')
	
	        ->when($startTime ?? 0, function ($query) use ($startTime) {
	
	            return $query->whereTime('deal_time', '>=', $startTime);
	
	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {
	
	            return $query->whereTime('deal_time', '<=', $endTime);
	
	        },function ($query) use ($endTime) {
	
	            return $query->whereMonth('deal_time');
	
	        })
	
	        ->group('r.shop_id')->select()->toArray();
	
			// print_r($refund);
			// exit;
	
	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');
	
	
		//门诊转店到 门店的 通过门店的归属ID 查询	--收款单 --03
		$receive_zd =Db::name('shop_refund_order')
	
		    ->field('sum(pay_amount * coefficient) as pay_amount,id,referral_shop_id as shop_id')
	
			->where($where)
	
		    // ->where('referral_shop_id','=',$shopId)
			
			->where('uid','in',$ids)
			
		    ->where('type','=',3)
	
		    ->where('status','=',2)
	
		    ->where('refund_type','=','101.02')
	
		    ->when($startTime ?? 0, function ($query) use ($startTime) {
	
		        return $query->whereTime('deal_time', '>=', $startTime);
	
		    })
	
		    ->when($endTime ?? 0, function ($query) use ($endTime) {
	
		        return $query->whereTime('deal_time', '<=', $endTime);
	
		    },function ($query) use ($endTime) {
	
		        return $query->whereMonth('deal_time');
	
		    })->group('referral_shop_id')->select()->toArray();
	
	
		//退款单是店转的 疤痕/项目业绩查询  --04
		$refund_dz = Db::name('shop_refund_order')->alias('r')
	
	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')
	
	        ->field('sum(g.pay_price * r.coefficient) as pay_amount,r.shop_id')
	
	        ->where($wheret)
	
	        // ->where('r.shop_id','=',$shopId)
			
			->where('r.uid','in',$ids)
			
	        ->where('g.refund_mode','=','06')
	
	        ->where('r.type','=',1)
	
	        ->where('r.status','=',2)
	
	        ->where('r.refund_type','<>','101.03')
	
	        ->when($startTime ?? 0, function ($query) use ($startTime) {
	
	            return $query->whereTime('deal_time', '>=', $startTime);
	
	        })
	
	        ->when($endTime ?? 0, function ($query) use ($endTime) {
	
	            return $query->whereTime('deal_time', '<=', $endTime);
	
	        },function ($query) use ($endTime) {
	
	            return $query->whereMonth('deal_time');
	
	        })
	
	        ->group('r.shop_id')->select()->toArray();
	
	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');
	
	
		//收款单是店转的 疤痕/项目业绩---05
		$receive_dz = Db::name('shop_refund_order')->alias('r')
	
	        ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')
	
	        ->field('sum(g.pay_price * r.coefficient) as pay_amount,r.shop_id')
	
	        // ->where('r.shop_id','=',$shopId)
	
	        ->where($wheret)
			
			->where('r.uid','in',$ids)
			
	        ->where('g.refund_mode','=','06')
	
	        ->where('r.type','=',3)
	
	        ->where('r.status','=',2)
	
	        ->where('r.refund_type','=','101.02')
	
	        ->when($startTime ?? 0, function ($query) use ($startTime) {
	
	            return $query->whereTime('deal_time', '>=', $startTime);
	
	        })
	        ->when($endTime ?? 0, function ($query) use ($endTime) {
	
	            return $query->whereTime('deal_time', '<=', $endTime);
	
	        },function ($query) use ($endTime) {
	
	            return $query->whereMonth('deal_time');
	
	        })
	
	        ->group('r.shop_id')->select()->toArray();
	
	        // ->column('r.pay_amount * r.coefficient as pay_amount','r.id');
	
	
		//门诊收款单 是非疗程的 疤痕/项目业绩  --06
		$receive_mz =Db::name('shop_refund_order')
	
		    ->field('sum(pay_amount * coefficient) as pay_amount,shop_id')
	
			->where($where)
	
		    // ->where('shop_id','=',$shopId)
			
			->where('uid','in',$ids)
			
			->where('is_treatment','=',2)
	
		    ->where('type','=',3)
	
			->where('referral_shop_id','>',0)
	
		    ->where('status','=',2)
	
		    ->where('refund_type','<>','101.03')
	
		    ->when($startTime ?? 0, function ($query) use ($startTime) {
	
		        return $query->whereTime('deal_time', '>=', $startTime);
	
		    })
	
		    ->when($endTime ?? 0, function ($query) use ($endTime) {
	
		        return $query->whereTime('deal_time', '<=', $endTime);
	
		    },function ($query) use ($endTime) {
	
		        return $query->whereMonth('deal_time');
	
		    })->group('shop_id')->select()->toArray();
	
		$data = [];
		foreach($receive as $value)
		{
			$data[$value['shop_id']]['receive']=$value['pay_amount'];
		}
	
		foreach($refund as $value)
		{
			$data[$value['shop_id']]['refund']=$value['pay_amount'];
		}
	
		foreach($receive_dz as $value)
		{
			$data[$value['shop_id']]['receive_dz']=$value['pay_amount'];
		}
		foreach($receive_mz as $value)
		{
			$data[$value['shop_id']]['receive_mz']=$value['pay_amount'];
		}
		foreach($refund_dz as $value)
		{
			$data[$value['shop_id']]['refund_dz']=$value['pay_amount'];
		}
		foreach($receive_zd as $value)
		{
			$data[$value['shop_id']]['receive_zd']=$value['pay_amount'];
		}
		foreach($data as $key => $value)
		{
			if(empty($value['refund']))
			{
				$data[$key]['refund']=0;
			}
			if(empty($value['receive']))
			{
				$data[$key]['receive']=0;
			}
			if(empty($value['receive_dz']))
			{
				$data[$key]['receive_dz']=0;
			}
			if(empty($value['receive_mz']))
			{
				$data[$key]['receive_mz']=0;
			}
			if(empty($value['refund_dz']))
			{
				$data[$key]['refund_dz']=0;
			}
			if(empty($value['receive_zd']))
			{
				$data[$key]['receive_zd']=0;
			}
		}
	    $result=[];
		if($shop_type==1){ //1是门诊 2是门店
			//return round($receive['pay_amount']-$refund['pay_amount'] +$receive_dz['pay_amount']+bcsub(0,$receive_mz['pay_amount'],2)+bcsub(0,$refund_dz['pay_amount']),2);
			// return round(($receive['pay_amount']-$refund['pay_amount'] +$receive_dz['pay_amount']-$receive_mz['pay_amount']-$refund_dz['pay_amount']),2);
			foreach($data as $key =>$value)
			{
				$result[$key] = round(($value['receive']-$value['refund'] +$value['receive_dz']-$value['receive_mz']-$value['refund_dz']),2);
			}
		}else{
			//实收金额 - 退款金额 + 门诊收钱分给店面的一半金额
			// return round(($receive['pay_amount']-$refund['pay_amount'] +$receive_zd['pay_amount']+$receive_dz['pay_amount']-$refund_dz['pay_amount']),2);
			foreach($data as $key =>$value)
			{
				$result[$key] = round(($value['receive']-$value['refund'] +$value['receive_zd']+$value['receive_dz']-$value['refund_dz']),2);
			}
		}
		return $result;
	}
	
	
	
}
